topic Transpose a table with non-unique IDs - create unknown number of columns in SAS Programming

Transpose a table with non-unique IDs - create unknown number of columns

kz_ — Fri, 26 Aug 2022 18:56:13 GMT

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
;

Re: Transpose a table with non-unique IDs - create unknown number of columns

Tom — Fri, 26 Aug 2022 19:14:00 GMT

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;

Re: Transpose a table with non-unique IDs - create unknown number of columns

kz_ — Fri, 26 Aug 2022 20:16:13 GMT

This is perfect! And so simple too. Thank you!