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;
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.