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)
|