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

[Justin]

这里的牛人多，再来请教一下。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]

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 3 years, 2 months ago by  Justin.