topic Get one row per ID and calculated for each ID metrics between rows in SAS Programming

Get one row per ID and calculated for each ID metrics between rows

Ronein — Sun, 04 Aug 2019 09:23:24 GMT

Hello

I have 3 rows for each ID.

I need to create a table that have 1 row for each ID with following information:

From field "GK" will take the value in first row

From field "W" will take the accumulated value in all rows of ID

From Field "W" will take the accumulated value in all rows

From field "Y" will take the difference between last row and first row

Desired output will be:

ID GK_First Accum_W_Per_ID Accum_W_All Y_Diff_Last_First

1 7 30 50 3

2 10 20 50 5

Data aaa;
input ID mon x w y ;
cards;
1 1812 7 10 2
1 1903 8 15 3
1 1906 8 5  5
2 1812 10 7 4
2 1903 10 8 8
2 1906 9  5 9
;
run;

Re: Get one row per ID and calculated for each ID metrics between rows

PaigeMiller — Sun, 04 Aug 2019 11:14:10 GMT

data bbb;
    set aaa;
    by id;
    if first.id then first=1; else first=0;
    if last.id then last=1; else last=0;
    y1=y;
run;

proc summary data=bbb;
    class id;
    var x/weight=first;
    var y/weight=first;
    var y1/weight=last;
    var w;
    output out=ccc sum=;
run;

data want;
    if _n_=1 then set ccc(where=(_type_=0) keep=w _type_ rename=(w=accum_w_all));
    set ccc(where=(_type_=1));
    gk_first=x;
    accum_w_id=w;
    y_diff_last_first=y1-y;
    drop w x y y1 _freq_ _type_;
run;

Re: Get one row per ID and calculated for each ID metrics between rows

Ksharp — Sun, 04 Aug 2019 12:18:09 GMT

Data aaa;
input ID mon x w y ;
cards;
1 1812 7 10 2
1 1903 8 15 3
1 1906 8 5  5
2 1812 10 7 4
2 1903 10 8 8
2 1906 9  5 9
;
run;
data temp;
 set aaa;
 by id;
 retain GK_First y_first;
 if first.id then do; GK_First=x;y_first=y;Accum_W_Per_ID=0;end;
 Accum_W_Per_ID+w;
 if last.id then do; Y_Diff_Last_First=y-y_first;output;end;
 keep ID   GK_First   Accum_W_Per_ID       Y_Diff_Last_First ;
run;
proc sql;
create table want as
select *,sum(Accum_W_Per_ID) as Accum_W_All  
 from temp;
quit;