各位老师好:
请问以下3个sql能否合并成1个sql实现?
或者以下3个sql是否能用1个sql优化一下?
***总是感觉自己写的代码繁琐的看不下去有不知如何优化更合适。;
谢谢。
说明:
1、flw20140630数据集中同个id(card_id or var1)有多条检测记录;
2、card数据集是每条id对应的除检测以外的其他信息;
3、将card中与flw20140630有相同id的首次与末次检测结果进行匹配,
但要保留card中所有的id(要知道有多少id有检测结果)。
***2.1:选择首次cd4检测结果与检测日期;
proc sql;
create table flwfirst as
select var1,var53,var54,var57,var58,var59,var60,var61,
input(compress(var1,"‘’"),$40.) as cardid
from epi.flw20140630
where var57 is not missing
group by input(compress(var1,"‘’"),$40.)
having var58=min(var58)
;
quit;
proc sort data=flwfirst out=flwfirst nodupkey;
by cardid var58;
run;
***2.2:选择末次cd4检测结果与检测日期;
proc sql;
create table flwlast as
select var1,var53,var54,var57,var58,var59,var60,var61
from epi.flw20140630
where var57 is not missing
group by input(compress(var1,"‘’"),$40.)
having var58=max(var58)
;
quit;
proc sort data=flwlast out=flwlast(rename=(var1=var1_ var53=var53_ var54=var54_
var57=var57_ var58=var58_ var59=var59_ var60=var60_ var61=var61_)) nodupkey;
by var1 var58;
run;
proc sql;
create table cardflw as
select a.*,
b.*,
c.*
from card(where=(areatype not in ("港澳台" "外籍") and
flagoval eq "已终审卡" and
typeocase in ("实验室诊断病例" "临床诊断病例") and
dateotype between '01jul1985'd and '30jun2014'd and
int(addcode/1000000) eq 44)) as a
left join flwfirst as b
on input(compress(a.card_id,"‘’"),$40.) = input(compress(b.var1,"‘’"),$40.)
left join flwlast as c
on input(compress(a.card_id,"‘’"),$40.) = input(compress(c.var1_,"‘’"),$40.);
quit;
proc sort data=cardflw out=cardflw nodupkey;
by card_id;
run;