Transpose Account Data which has multiple entries into multiple columns.

[KMWWN156]

I am looking to transpose account level date which currently has one row per entry, with upto 5 rows per account as i am interested in the last 5 payments for an account, some accounts have less than 5 payments.

I want to transpose this so each account has one row, with the payment data across multiple columns.

Data is currently like this.

Accno	TranDate	TranAmt	TranCode
Account1	21Jul2026	75.26	53
Account2	24Dec2021	26.86	53
Account3	16May2014	40.00	53
Account3	16Jan2015	40.00	53
Account3	16Sep2015	40.00	53
Account3	16Oct2015	40.00	53
Account4	03Mar2023	20.00	53
Account5	02Nov2020	1.00	53
Account5	02Dec2020	1.00	53
Account5	31Dec2020	1.00	53
Account5	02Feb2021	1.00	53
Account5	02Mar2021	1.00	53

And i want it to appear like this.

Accno	TranDate1	TranAmt1	TranCode1	TranDate2	TranAmt2	TranCode2	TranDate3	TranAmt3	TranCode3	TranDate4	TranAmt4	TranCode4	TranDate5	TranAmt5	TranCode5
Account1	21Jul2026	75.26	53
Account2	24Dec2021	26.86	53
Account3	16May2014	40.00	53	16Jan2015	40.00	53	16Sep2015	40.00	53	16Oct2015	40.00	53
Account4	03Mar2023	20.00	53
Account5	02Nov2020	1.00	53	02Dec2020	1.00	53	31Dec2020	1.00	53	02Feb2021	1.00	53	02Mar2021	1.00	53

Thanks,

[Kurt_Bremser]

What do you intend to do with this data? Keep in mind that, for most purposes, the original long layout is better than wide.

[KMWWN156]

It is being provided to a stakeholder alongside other account information, and the payment data has been asked for in the requested format above. Thanks

[PaigeMiller]

You can use PROC TRANSPOSE as in this example: 

https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.4/proc/n01rzqgzs8vq1bn10h1wtx914kyf.htm

If you want actual code, you will need to provide data in a usable form, as seen in these examples and instructions.

PS: I understand that a key person wants the data in this form; but please understand that is the only reason to do this. If you are programming for yourself, and want to use the results in further analyses, tables or plots, you would never do this transpose. As stated by @Kurt_Bremser , this transposed arrangement of the data makes all other programming much more difficult.

[Kurt_Bremser]

Can there be groups with more than 5 observations?

[data_null__]

You need a variable to make the transactions unique within the accno.

data test;
   infile cards dsd dlm='09'x firstobs=2;
   input Accno $ TranDate:date9. TranAmt TranCode;
   format trandate date11.;
   datalines;
Accno	TranDate	TranAmt	TranCode
Account1	21-Jul-26	75.26	53
Account2	24-Dec-21	26.86	53
Account3	16-May-14	40	53
Account3	16-Jan-15	40	53
Account3	16-Sep-15	40	53
Account3	16-Oct-15	40	53
Account4	3-Mar-23	20	53
Account5	2-Nov-20	1	53
Account5	2-Dec-20	1	53
Account5	31-Dec-20	1	53
Account5	2-Feb-21	1	53
Account5	2-Mar-21	1	53
;;;;
   run;
data test;
   set test;
   by accno;
   if first.accno then ndx = 0;
   ndx + 1;
proc print;
   run;
proc transpose data=test out=tall name=vname;
   by accno ndx;
   var tran:;
   run;
proc print;
   run;
proc transpose data=tall out=wide(drop=_name_);
   by accno;
   var col1;
   id vname ndx;
   format trandate: date11.;
   run;
proc print;
   run;

[Ksharp]

The most simple way is using PROC SUMMARY.

data test;
   infile cards truncover expandtabs;
   input Accno $ TranDate:date9. TranAmt TranCode;
   format trandate date11.;
   datalines;
Account1	21-Jul-26	75.26	53
Account2	24-Dec-21	26.86	53
Account3	16-May-14	40	53
Account3	16-Jan-15	40	53
Account3	16-Sep-15	40	53
Account3	16-Oct-15	40	53
Account4	3-Mar-23	20	53
Account5	2-Nov-20	1	53
Account5	2-Dec-20	1	53
Account5	31-Dec-20	1	53
Account5	2-Feb-21	1	53
Account5	2-Mar-21	1	53
;;;;
proc sql noprint;
select max(n) into :n from
(select count(*) as n from test group by Accno);
quit;
proc summary data=test;
by Accno;
output out=want idgroup(out[&n] (TranDate TranAmt TranCode)=);
run;