SAS-Sql的这些用法你都会了么？

proc sql noprint;/*来申明Proc sql 过程步*/
create table report_ as  /*创建一个表*/
select distinct *    /*选出需要的变量或者字段*/
from    ; /*变量或者字段来自哪一个表*/
quit;
*distinct  去掉重复观测的选项;

proc sql noprint;
create table report_ as
select distinct *
from    as a

left join    as b
on a. =b. 
and a. =b. 
group by 
order by 
;
quit;

proc sql noprint;
create table Temp1 as
select distinct siteid,count(*) as num label="完成试验人数"
from   raw.ds where dscompyn='1'
group by siteid
order by siteid;
create table Temp2 as
select "总计" as siteid,count(*) as num
from raw.ds where dscompyn='1';
create table Temp as
select * from Temp1
union all
select * from Temp2;
quit;

proc freq data=Raw.DS noprint; 
where dscompyn='1';
table  siteid/chisq nopercent norow nocol   out=aaa1(drop=PERCENT);
run;

proc means data = raw.ds noprint;
where dscompyn='1';
class Siteid;
output out=temp3(drop=_FREQ_ )  n = cnt ;
run;

proc sql noprint;
create table need1(where=(age=max_age)) as
select distinct usubjid,int((input(rficdtc,yymmdd10.)-input(brthdtc,yymmdd10.))/365.25) as age,
Max(int((input(rficdtc,yymmdd10.)-input(brthdtc,yymmdd10.))/365.25)) as Max_age
from    Raw.dm
;
quit;

proc sql noprint;
create table need2 as
select distinct usubjid,int((input(rficdtc,yymmdd10.)-input(brthdtc,yymmdd10.))/365.25) as age,
Max(int((input(rficdtc,yymmdd10.)-input(brthdtc,yymmdd10.))/365.25)) as Max_age
from    Raw.dm where age=max_age;
quit;

proc sql noprint;
create table temp4 as
select distinct *
from  raw.ds where usubjid in (select usubjid from raw.ae where aeyn='1')
;
quit;

proc sql UNDO_POLICY=NONE;
create table report_ as
select distinct a.siteid format=$200.,a.Count label="筛选人数" ,d.Count as Count1   label="筛选失败人数",
e.Count as Count2   label="随机人数" ,b.COUNT as COUNT3 label="完成研究人数",c.COUNT as COUNT4 label="提前中止研究人数"
from  aaa2   as a
left join   aaa1  as b
on a.siteid =b.siteid
left join  aaa3  as c
on a.siteid =c.siteid
left join  aaa4  as d
on a.siteid =d.siteid
left join  aaa5  as e
on a.siteid =e.siteid
;
quit;

proc sql UNDO_POLICY=NONE;/*where语句核查情况*/
          create table Chk as
            select distinct &&ChkMainDATA&i...&SubjidKey.,&&ChkMainDATA&i...&VisitKey. ,&&ChkMainDATA&i...&PageKey. ,
            "&&ChkDvpCode&i." as DVP_CODE length=200,"&&ChkDesc&i." as DECL length=2000,&&ChkQUERY&i. as Query length=2000
              from &&ChkMainDATA&i. 
          %if %length(&CDataSet1.)^=0 %then %do;
            full join &CDataSet1. on &&ChkMainDATA&i...&SubjidKey.=&CDataSet1..&SubjidKey.
          %end;
          %if %length(&CDataSet2.)^=0 %then %do;
            full join &CDataSet2. on &&ChkMainDATA&i...&SubjidKey.=&CDataSet2..&SubjidKey.
          %end;
          %if %length(&CDataSet3.)^=0 %then %do;
            full join &CDataSet3. on &&ChkMainDATA&i...&SubjidKey.=&CDataSet3..&SubjidKey.
          %end;
          %if %length(&CDataSet4.)^=0 %then %do;
            full join &CDataSet4. on &&ChkMainDATA&i...&SubjidKey.=&CDataSet4..&SubjidKey.
          %end;
          %if %length(&CDataSet5.)^=0 %then %do;
            full join &CDataSet5. on &&ChkMainDATA&i...&SubjidKey.=&CDataSet5..&SubjidKey.
          %end;
          %if %length(&CDataSet6.)^=0 %then %do;
            full join &CDataSet6. on &&ChkMainDATA&i...&SubjidKey.=&CDataSet6..&SubjidKey.
          %end;
          where  &&ChkCondt&i.
          ;
      quit;