/* SQL-MACRO-描述统计练习 */

/* 1. SQL应用。先查看例子数据集结构。 */
libname p2 './practice2-2015';
* 由于原来的DEMO数据集版本与SAS9.2有差别，所以复制一份 ;
data demo1;
  set p2.demo;
run;
run;
* 转为CSV文件 ;
PROC EXPORT DATA=DEMO1
     OUTFILE= "c:\sasex\demo.csv" 
     DBMS=CSV REPLACE;
  PUTNAMES=YES;
RUN;

proc datasets lib=p2;
  contents data=demo;
  contents data=vs;
run;
quit;

proc contents data=p2.demo;
run;
quit;

proc print data=p2.demo(obs=20);
run;


/* 把库中的编码与标签用输出格式联系起来。 */
proc format;
  value sexf 1='男' 2='女';
  value marryf 1='未婚' 2='已婚' 3='离异' 4='丧偶';
  value educaf 1='初中以下' 2='初中' 3='高中'
      4='大专/大学' 5='大学以上';
  value occupf 1='学生' 2='兼职' 3='全职' 4='退休' 5='其它';
  value smokef 0='从不吸烟' 1='过去吸烟' 2='现在吸烟';
  value drinkf 0='从不饮酒' 1='过去饮酒' 2='现在饮酒';
run;
data demo;
  set p2.demo;
  birth = input(bthdtc, yymmdd10.);
  consent = input(condtc, yymmdd10.);
  age = int((consent-birth)/365.25);
  windex = wt/(ht/100)**2;
  attrib birth format=yymmdd10. label='出生日期'
    consent format=yymmdd10. label='知情同意日期'
	sex format=sexf. label='性别'
	marry format=marryf. label='婚姻状况'
    educa format=educaf. label='教育程度'
	occup format=occupf. label='职业'
	smoke format=smokef. label='吸烟'
	drink format=drinkf. label='饮酒'
	group label='组别'
	siteid label='中心'
	age label='年龄'
	ht label='身高'
	wt label='体重'
	windex label='体重指数'
  ;
  drop bthdtc condtc;
run;
proc print data=demo(obs=10) noobs label;
run;

/* SQL练习。 */
****** 1.1;
proc sql;
  select *, wt/(ht/100)**2 AS wind2
    from demo;
  select avg(wt/(ht/100)**2) AS avgwind
    from demo;
quit;
proc sql;
  create table demo2 as
    select *, wt/(ht/100)**2 AS wind2
      from demo;
quit;

****** 1.2;
proc sql;
  select int( (input(condtc, yymmdd10.)
    - input(bthdtc, yymmdd10.)) / 365.25) AS age2
    from p2.demo;
quit;
proc sql;
  select *, int((consent-birth)/365.25) AS age2
    from demo;
quit;
proc sql;
  create table agev as
    select *, age,
        case
          when age=. then ' '
          when age<18 then  '<18'
          when age<40 then '18-<40'
          when age<60 then '40-<60'
          when age>=60 then '>60'
	  else ' '
        end as ageg
      from demo;
  select * from agev;
quit;
*** 或者用FORMAT和INPUT函数分组 ;
proc format;
  VALUE agegf
    .=' '
    LOW-<18='<18'
    18-<40='18-<40'
    40-<60='40-<60'
    60-HIGH='>60'
  ;
run;
proc sql;
  create table agev as
    select *, PUT(age, agegf.) AS ageg
      from demo;
  select * from agev;
quit;

****** 1.3;
proc sql;
 select *
   from p2.vs
   where sbp is not null 
    and (sbp>140 or sbp<90);
quit;

****** 1.4;
proc sql;
 select *
   from p2.vs
   where dbp is not null 
    and (dbp>90 or dbp<60);
quit;

****** 1.5;
proc sql;
 select *
   from p2.vs
   where hr is not null and (hr>100 or hr<50);
quit;

****** 1.6;
proc sql;
 select *
   from p2.vs
   where sbp is not null 
    and dbp is not null
        and (sbp>140 and dbp>90);
quit;

****** 1.7;
proc sql;
 select *
   from p2.vs
   where sbp is not null 
    and dbp is not null
        and sbp-dbp<10;
quit;

****** 1.8;
proc sql;
  select *
    from p2.vs
      having wt is not null
       and (wt < avg(wt)-2*std(wt)
           or wt > avg(wt)+2*std(wt));
quit;
proc sql;
  select avg(wt)-2*std(wt) AS wlb, avg(wt)+2*std(wt) AS wub
      from p2.vs;
  create table _tmp AS
    select avg(wt)-2*std(wt) AS wlb, avg(wt)+2*std(wt) AS wub
      from p2.vs;
  select * 
    from p2.vs a, _tmp b
	where wt is not null and (wt<wlb or wt>wub);
  drop table _tmp;
quit;

****** 1.9;
proc sql outobs=20;
  select usubjid, visitnum, wt
    from p2.vs
    order by usubjid, visitnum;
quit;
proc sql;
  create table _tmp1 AS
    select usubjid, wt*1.05 AS wtlim
      from p2.vs
      where visitnum=1;
  select a.usubjid, a.visitnum, a.wt, wtlim
    from p2.vs AS a, _tmp1 AS b
    where a.usubjid=b.usubjid
          and a.wt>b.wtlim
    order by a.usubjid, a.visitnum;
quit;
proc sql;
  select a.usubjid, a.visitnum, a.wt, b.wt as basewt
    from p2.vs AS a, p2.vs AS b
    where a.usubjid=b.usubjid
          and b.visitnum=1
          and a.wt>b.wt*1.05
    order by a.usubjid, a.visitnum;
quit;


****** 1.10;
proc sql;
   select a.usubjid,
      a.visitnum AS firstv, a.wt AS firstwt,
      b.visitnum AS secondv,b.wt AS secondwt
    from p2.vs AS a, p2.vs AS b
    where a.usubjid=b.usubjid
      and a.visitnum<b.visitnum
      and a.wt < b.wt
	order by a.usubjid, firstv, secondv
  ;
quit;
proc sql;
  select distinct usubjid, firstwt, secondwt
   from(
   select a.usubjid,
      a.visitnum AS firstv, a.wt AS firstwt,
      b.visitnum AS secondv,b.wt AS secondwt
    from p2.vs AS a, p2.vs AS b
    where a.usubjid=b.usubjid
      and a.visitnum<b.visitnum
      and a.wt < b.wt)
    order by usubjid;
quit;


/* 2. 判断下面宏变量的结果 */
%let value1=982;
%let value2=813;
%let result=&value1 + &value2;
%put &result;

%let holdvars=varnames;
%put &holdvars;
%let &holdvars=title author datesold;
%put &holdvars;
%put &varnames;


%let section1=Certification and Training;
%let section2=Internet;
%let section3=Networking and Communication;
%let section4=Operating Systems;
%let section5=Programming and Applications;
%let section6=Web Design;
%let n=4;
%put &section&n;
%put &&section&n;


/* 描述统计。 */
proc sort data=demo;
  by group;
run;

proc freq data=demo;
  tables sex * group;
run;
* 或者: ;
proc freq data=demo;
  tables sex;
  where group='A';
run;
proc freq data=demo;
  tables sex;
  where group='B';
run;
* 或者: ;
proc freq data=demo;
  tables sex;
  BY group;
run;

proc means data=p2.demo N MEAN STD MEDIAN Q1 Q3 MIN MAX;  var wt;
class group;  run;

/* CMH 统计：控制分层变量后，检验行变量与列变量的独立性。
** 不需要为分层变量估计参数。下例中siteid是分层变量。
*/
proc freq data=demo;
  tables siteid*group;
proc freq data=demo;
  tables sex*group;
  tables siteid*sex*group / cmh;
run;
quit;

proc means data=demo n mean std median q1 q3 min max;
  class group;
  var age;
run;
proc sort data=demo;
  by group;
run;
proc boxplot data=demo;
  plot age*group;
run;
proc ttest data=demo;
  class group;
  var age;
run;
proc glm data=demo;
  class group siteid;
  model age=group siteid group*siteid;
quit;
proc glm data=demo;
  class group siteid;
  model age=group siteid;
quit;
proc glm data=demo;
  class group siteid;
  model age=siteid;
run;
quit;

/* 练习4。方差分析 */
data blood;
  do g='正常','轻度', '重度';
    do i=1 to 10;
      input y @@;
      lgy = log10(y);
      output;
    end;
  end;
  drop i;
  datalines;
234  318  402  382  621  408  243  141  42  98
509  518  555  758  845  712  585  448  753  896
851  562  918  631  653  843  659  849  762  901
;
run;
proc print;run;

proc sort data=blood;
  by g;
run;
proc boxplot data=blood;
  plot y * g;
  plot lgy * g;
run;
quit;

proc anova data=blood;
  class g;
  model y = g;
  means g / t;
run;
quit;
proc anova data=blood;
  class g;
  model lgy = g;
  means g / t;
run;
quit;
