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

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

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

    Administrator
    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

    Administrator
    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.

Log in to reply.

Original Post
0 of 0 posts June 2018
Now