I would like to transpose the 'have' table so that there is one row per ID. I would like to order the events by date, so that 'type1' corresponds to the type of event that happened first and 'type2' to the type of event that happened second, etc. I don't necessarily know how many rows there are per ID.
Thank you for your help! I still find proc transpose to be confusing.
data have; input id type $ (date) (:yymmdd.) amount; format date yymmdd10.; datalines; 1 A 2019/01/01 100 1 B 2019/10/01 200 1 A 2018/10/01 200 2 C 2019/08/01 150 2 A 2020/08/08 250 2 C 2021/08/08 200 2 B 2022/08/08 250 ; data want; input id type1 $ (date1) (:yymmdd.) amount1 type2 $ (date2) (:yymmdd.) amount2 type3 $ (date3) (:yymmdd.) amount3 type4 $ (date4) (:yymmdd.) amount4; format date1 date2 date3 date4 yymmdd10.; datalines; 1 A 2018/10/01 200 A 2019/01/01 100 B 2019/10/01 200 . . . 2 C 2019/08/01 150 A 2020/08/08 250 C 2021/08/08 200 B 2022/08/08 250 ;
That is not what PROC TRANSPOSE is designed to handle.
Either write your own data step, probably using ARRAYs.
Or use PROC SUMMARY with IDGROUP. https://support.sas.com/resources/papers/proceedings10/102-2010.pdf
You need to tell PROC SUMMARY how many copies of the variables you want to create.
You could just hard code some maximum value, like 4, in this case.
data have;
input id type $ date :yymmdd. amount;
format date yymmdd10.;
datalines;
1 A 2019/01/01 100
1 B 2019/10/01 200
1 A 2018/10/01 200
2 C 2019/08/01 150
2 A 2020/08/08 250
2 C 2021/08/08 200
2 B 2022/08/08 250
;
%let max=4 ;
proc summary data=have;
by id;
output out=want idgroup(out[&max] (type date amount)=);
run;
Result:
If you need to count the maximum number of copies then you can add an SQL step before the PROC SUMMARY step.
proc sql noprint;
select max(n) into :max trimmed
from (select id,count(*) as n from have group by id)
;
quit;
That is not what PROC TRANSPOSE is designed to handle.
Either write your own data step, probably using ARRAYs.
Or use PROC SUMMARY with IDGROUP. https://support.sas.com/resources/papers/proceedings10/102-2010.pdf
You need to tell PROC SUMMARY how many copies of the variables you want to create.
You could just hard code some maximum value, like 4, in this case.
data have;
input id type $ date :yymmdd. amount;
format date yymmdd10.;
datalines;
1 A 2019/01/01 100
1 B 2019/10/01 200
1 A 2018/10/01 200
2 C 2019/08/01 150
2 A 2020/08/08 250
2 C 2021/08/08 200
2 B 2022/08/08 250
;
%let max=4 ;
proc summary data=have;
by id;
output out=want idgroup(out[&max] (type date amount)=);
run;
Result:
If you need to count the maximum number of copies then you can add an SQL step before the PROC SUMMARY step.
proc sql noprint;
select max(n) into :max trimmed
from (select id,count(*) as n from have group by id)
;
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.