Macro和sql

是 否 +2 论坛币

k人 参与回答

经管之家送您一份

应届毕业生专属福利!

求职就业群

赵安豆老师微信：zhaoandou666

经管之家联合CDA

送您一个全额奖学金名额~ !

立即领取

感谢您参与论坛问题回答

经管之家送您两个论坛币！

+2 论坛币

如果从事初级sas编程（比如临床试验编程）会用很多proc sql或macro吗？

扫码加我 拉你入群

请注明：姓名-公司-职位

以便审核进群资格，未注明则拒绝

分享0 收藏1 回帖

关键词：Macro acr CRO Mac sql 临床试验

临床试验是会用到sql、macro、base以及统计分析。
但你是怎么界定临床试验SAS应用为初级编程的，很想知道！

sql 语言不是必须的。

3# elek.me 恩，应该不是必须的，但是现在很多临床试验统计分析培训班都把SQL作为临床试验SAS编程的一个重要部分来讲解。

我应该这样问：如果从事临床试验初级编程会用很多proc sql或macro吗？

以我的工作经验，这两项是必须的!

proc sql 和 macro 是两个不同的东西，我觉得不该放在一起来讲。
proc sql只是base sas里的一个proc而已。其它procs照样用途也很广泛，比如说 proc means等等。
很多人喜欢用sql，可能跟他们之前的其它数据库编程有关。
不管怎么样，把sas的基本语言学扎实，才是最重要的。

Thanks guys

请大家继续发表意见

1。proc sql解决的是两个以上数据文件相互合并的问题。用proc sql能更好的解决merge;by;所存在的不足；
举个例子来说（合并bp和filled，用merge和sql结果是不一样的，在这个例子中，sql比merge更完整全面，但增加了重复性，需要在合并后，剔除那些不必要和多余的重复纪录；但merge却可能完全丢失一些应该收录的纪录而影响最重的结果）：
*title1 'Using BP Reading to Select Dates of Fills';
data BP;
   input id BPlstdt date7.;
   format BPlstdt date7.;
cards;
1 13JUN00
1 01MAY01
2 15SEP00
2 07MAY01
3 04MAY01
;

data fills;
   input id frp_date date7.;
   format frp_date date7.;
cards;
1 19JAN00
1 20MAR00
1 21APR01
1 22JUL01
2 23OCT00
2 24FEB01
2 25AUG01
3 26JAN00
3 27MAR00
3 28DEC00
;
run;

*  *  *  *  *;
*title2 '* Complete Merge *';
data final_data0;
   merge BP fills;
   by id;
proc print data=final_data0;
   var id frp_date BPlstdt;
run;

*  *  *  *  *;
*title2 '* 1st Way: Date Criterion, Current Way *';
data combined;
   merge BP fills;
   by id;
   date_difference=BPlstdt-frp_date;
data final_data1;
   set combined;
   if abs(date_difference)<=365;
proc print data=final_data1;
   var id frp_date BPlstdt date_difference;
   *title4 'NOTE:  For Patient #1, fill for 20MAR00 is not included although it should be';
run;

*  *  *  *  *;
*title2 '* 2nd Way: Date Criterion - For each patient, match every BP reading with every fill *';
* this way allows a fill to qualify if any of the BP readings is within 1 year;
proc sql;
   CREATE TABLE combined AS
   SELECT BP.*, fills.*
   FROM BP INNER JOIN fills
   ON BP.ID = fills.ID;
data combined;
   set combined;
   date_difference=BPlstdt-frp_date;
proc print data=combined;
   var id frp_date BPlstdt date_difference;
   *title4 'Result of Using PROC SQL - MANY RECORDS';
run;

data temp;
   set combined;
   if abs(date_difference)<=365;
proc print data=temp;
   var id frp_date BPlstdt date_difference;
   *title4 'Records Meeting Date Criterion, ALLOWING DUPLICATES';
run;

* remove duplicates of fill records.  Note that BP date is irrelevant at this point;
proc sort data=temp out=final_data2 nodupkey;
   by id frp_date;
proc print data=final_data2;
   var id frp_date BPlstdt date_difference;
   *title4 'Records Meeting Date Criterion, DUPLICATES REMOVED';
run;
2。在临床试验中可能会有很多的不同的样本或不同的参数进行相关相应的重复试验，这时候选用macro作为一个统一的（templete）模式就会十分省力，写一段macro可以会重复使用以避免写出很多重复的语句而精炼程序：
%macro table1(in1,in2,out,var1,var2,var3,var4,var5);
data &out(keep=id &var1 &var2 &var3);
   merge den_dm0006a1c2(in=a) &in1(keep=id in=b)
         &in2(keep=id in=c);
   by id;
   array x(*) &var1 &var2 &var3;
   length &var1 &var2 &var3 3;
   if a;
   do i=1 to dim(x);
      if x=. then x{i}=0;
   end;drop i;
   if &var4=1 and &var5=1 and b then &var1=1;
   if &var4=1 and &var5=1 and c and not b then &var2=1;
   if &var4=1 and &var5=1 and not b and not c then &var3=1;
   if &var4=1 and &var5=1 then output &out;
proc freq data=&out;
   tables &var1 &var2 &var3;
run;
%mend table1;
%table1(low_a1c00cmb,ctr_ind00cmb,prvl_ongo00,prvl_ongo_low_a1c00,
        prvl_ongo_ctr_ind00,prvl_ongo_no_mtg00,prvl00,ongo00)
%table1(low_a1c01cmb,ctr_ind01cmb,prvl_ongo01,prvl_ongo_low_a1c01,
        prvl_ongo_ctr_ind01,prvl_ongo_no_mtg01,prvl01,ongo01)
%table1(low_a1c02cmb,ctr_ind02cmb,prvl_ongo02,prvl_ongo_low_a1c02,
        prvl_ongo_ctr_ind02,prvl_ongo_no_mtg02,prvl02,ongo02)
%table1(low_a1c03cmb,ctr_ind03cmb,prvl_ongo03,prvl_ongo_low_a1c03,
        prvl_ongo_ctr_ind03,prvl_ongo_no_mtg03,prvl03,ongo03)
%table1(low_a1c04cmb,ctr_ind04cmb,prvl_ongo04,prvl_ongo_low_a1c04,
        prvl_ongo_ctr_ind04,prvl_ongo_no_mtg04,prvl04,ongo04)
%table1(low_a1c05cmb,ctr_ind05cmb,prvl_ongo05,prvl_ongo_low_a1c05,
        prvl_ongo_ctr_ind05,prvl_ongo_no_mtg05,prvl05,ongo05)
%table1(low_a1c06cmb,ctr_ind06cmb,prvl_ongo06,prvl_ongo_low_a1c06,
        prvl_ongo_ctr_ind06,prvl_ongo_no_mtg06,prvl06,ongo06)
%table1(low_a1c00cmb,ctr_ind00cmb,prvl_new00,prvl_new_low_a1c00,
        prvl_new_ctr_ind00,prvl_new_no_mtg00,prvl00,new00)*0;
%table1(low_a1c01cmb,ctr_ind01cmb,prvl_new01,prvl_new_low_a1c01,
        prvl_new_ctr_ind01,prvl_new_no_mtg01,prvl01,new01)*0;
%table1(low_a1c02cmb,ctr_ind02cmb,prvl_new02,prvl_new_low_a1c02,
        prvl_new_ctr_ind02,prvl_new_no_mtg02,prvl02,new02)*0;
%table1(low_a1c03cmb,ctr_ind03cmb,prvl_new03,prvl_new_low_a1c03,
        prvl_new_ctr_ind03,prvl_new_no_mtg03,prvl03,new03)*0;
%table1(low_a1c04cmb,ctr_ind04cmb,prvl_new04,prvl_new_low_a1c04,
        prvl_new_ctr_ind04,prvl_new_no_mtg04,prvl04,new04)*0;
%table1(low_a1c05cmb,ctr_ind05cmb,prvl_new05,prvl_new_low_a1c05,
        prvl_new_ctr_ind05,prvl_new_no_mtg05,prvl05,new05)*0;
%table1(low_a1c06cmb,ctr_ind06cmb,prvl_new06,prvl_new_low_a1c06,
        prvl_new_ctr_ind06,prvl_new_no_mtg06,prvl06,new06)*0;
%table1(low_a1c00cmb,ctr_ind00cmb,incd_ongo00,incd_ongo_low_a1c00,
        incd_ongo_ctr_ind00,incd_ongo_no_mtg00,incd00,ongo00)
%table1(low_a1c01cmb,ctr_ind01cmb,incd_ongo01,incd_ongo_low_a1c01,
        incd_ongo_ctr_ind01,incd_ongo_no_mtg01,incd01,ongo01)
%table1(low_a1c02cmb,ctr_ind02cmb,incd_ongo02,incd_ongo_low_a1c02,
        incd_ongo_ctr_ind02,incd_ongo_no_mtg02,incd02,ongo02)
%table1(low_a1c03cmb,ctr_ind03cmb,incd_ongo03,incd_ongo_low_a1c03,
        incd_ongo_ctr_ind03,incd_ongo_no_mtg03,incd03,ongo03)
%table1(low_a1c04cmb,ctr_ind04cmb,incd_ongo04,incd_ongo_low_a1c04,
        incd_ongo_ctr_ind04,incd_ongo_no_mtg04,incd04,ongo04)
%table1(low_a1c05cmb,ctr_ind05cmb,incd_ongo05,incd_ongo_low_a1c05,
        incd_ongo_ctr_ind05,incd_ongo_no_mtg05,incd05,ongo05)
%table1(low_a1c06cmb,ctr_ind06cmb,incd_ongo06,incd_ongo_low_a1c06,
        incd_ongo_ctr_ind06,incd_ongo_no_mtg06,incd06,ongo06)
%table1(low_a1c00cmb,ctr_ind00cmb,incd_new00,incd_new_low_a1c00,
        incd_new_ctr_ind00,incd_new_no_mtg00,incd00,new00)
%table1(low_a1c01cmb,ctr_ind01cmb,incd_new01,incd_new_low_a1c01,
        incd_new_ctr_ind01,incd_new_no_mtg01,incd01,new01)
%table1(low_a1c02cmb,ctr_ind02cmb,incd_new02,incd_new_low_a1c02,
        incd_new_ctr_ind02,incd_new_no_mtg02,incd02,new02)
%table1(low_a1c03cmb,ctr_ind03cmb,incd_new03,incd_new_low_a1c03,
        incd_new_ctr_ind03,incd_new_no_mtg03,incd03,new03)
%table1(low_a1c04cmb,ctr_ind04cmb,incd_new04,incd_new_low_a1c04,
        incd_new_ctr_ind04,incd_new_no_mtg04,incd04,new04)
%table1(low_a1c05cmb,ctr_ind05cmb,incd_new05,incd_new_low_a1c05,
        incd_new_ctr_ind05,incd_new_no_mtg05,incd05,new05)
%table1(low_a1c06cmb,ctr_ind06cmb,incd_new06,incd_new_low_a1c06,
        incd_new_ctr_ind06,incd_new_no_mtg06,incd06,new06)