Home Forums Chinese Forum 大数据分析 在SAS中如何得到下面的结果？

• # 在SAS中如何得到下面的结果？

Justin updated 1 year, 3 months ago 1 Member · 2 Posts
• ### Justin

February 7, 2021 at 4:45 pm

这里的牛人多，再来请教一下。data set 如下：

`data test; input id \$1 v1 3 v2 \$5-6 v3 8 v4 \$10-11; cards; a 1 x2 .   a .    3   a .    . x4 b 1 x2 .   b .    3   b .    . x4 ;run;`

我想得到如下的data set：

id v1 v2 v3 v4
a 1 x2 3 x4
b 1 x2 3 x4

试了几种办法都不行，求教大牛们，谢谢！

• ### Justin

February 7, 2021 at 4:51 pm

Method A:

` proc sort data=test; by ID; run;`

` data B; set test; by ID; retain Z1-Z4; if not missing(V1) then Z1=V1; if not missing(V2) then Z2=V2; if not missing(V3) then Z3=V3; if not missing(V4) then Z4=V4; if last.ID; drop V1-V4; rename Z1=V1 Z2=V2 Z3=V3 Z4=V4; run;`

Method B:

`proc sql; select ID,         max(V1) as V1,        max(V2) as V2,         max(V3) as V3,         max(V4) as V4`
`from test group by ID;quit;`

Method C:

`%let NM=is not missing; data one(keep=id v1 v2 where=(v1 &NM))      two(keep=v3 where=(v3 &NM))      three(keep=v4 where=(v4 &NM));      set test; ;run;data all;    set one;        set two;    set three;run;%let NM=is not missing; data test2; merge test(keep=id v1 v2 where=(v1 &NM))       test(keep=v3  where=(v3 &NM))       test(keep=v4 where=(v4 &NM)); ;run;`

Method D

` %macro test; proc sql; create table var_ls as  select distinct name  from dictionary.columns where libname='WORK' and memname='TEST' and upcase(name) ne 'ID'; quit; data _null_; set var_ls; call symput(cats('var',_n_),name); call symput('cnt',_n_); run; proc sql; create table test2 as select id %do i = 1 %to &cnt; , max(&&var&i) as &&var&i %end; from test group by id;quit;%mend; %test;`

• This reply was modified 1 year, 3 months ago by  Justin.