How to restructure or reshape the dataset using the techniques such as retain and arrays

[anandrc]

Hello All,

I have a dataset as below. An account can have max 4 different dob/surnames. That is fixed.

Account DoB Surname

100A 01-Dec-17 ABC

100A 02-Dec-17 DEF

100A 03-Dec-17 GEH

100A 04-Dec-17 IJK

200B 01-May-17 LMN

200B 02-May-17 OPQ

300A 05-Jun-17 RST

Now, I want my output to look like

Account G1_Surname G1_DoB       G2_Surname   G2_DoB         G3_Surname    G3_DoB        G4_Surname  G4_DoB

100A   ABC        01-Dec-17 DEF         02-Dec-17  GEH         03-Dec-17 IJK         04-Dec-17

200B   LMN        01-May-17 OPQ         02-May-17

300A   RST        05-Jun-17

Can you suggest

[Reeza]

PROC TRANSPOSE

https://stats.idre.ucla.edu/sas/modules/how-to-reshape-data-wide-to-long-using-proc-transpose/

Or a data step, though I personally find that harder to manage:

https://stats.idre.ucla.edu/sas/modules/reshaping-data-wide-to-long-using-a-data-step/

And last but not least, there's a really good macro from some contributors on here:

http://www.sascommunity.org/wiki/A_Better_Way_to_Flip_(Transpose)_a_SAS_Dataset

[novinosrin · Accepted solution]

Subject was misleading to me for a bit-

/*requesting to frame the question like -- How to restructure  or reshape the dataset using the techniques constructs such as retain ,loop and transpose ? I am dumb sorry as I was wasting my time to see where;'s the loop or you meant the implicit loop or DOW or until end of file*/

Anyway:

 data have;

 input  Account $ DoB :date7. Surname $;

 format dob date7.;

 datalines;

100A 01-Dec-17 ABC

100A 02-Dec-17 DEF

100A 03-Dec-17 GEH

100A 04-Dec-17 IJK

200B 01-May-17 LMN

200B 02-May-17 OPQ

300A 05-Jun-17 RST

;

data want;

set have;

by account;

retain G1_Surname G1_DoB       G2_Surname   G2_DoB         G3_Surname    G3_DoB        G4_Surname  G4_DoB;

array t(*) $ G1_Surname        G2_Surname            G3_Surname            G4_Surname  ;

array y(*)G1_DoB G2_DoB G3_DoB G4_DoB;

if first.account then do;

     n=1;

     call missing(of t(*),of y(*));

     end;

else n+1;

t(n)=Surname;

y(n)=dob;

if last.account;

format G1_DoB G2_DoB G3_DoB G4_DoB date7.;

drop n DoB Surname  ;

run;

[Ksharp]

 data have;
 input  Account $ DoB :date7. Surname $;
 format dob date7.;
 datalines;
100A 01-Dec-17 ABC
100A 02-Dec-17 DEF
100A 03-Dec-17 GEH
100A 04-Dec-17 IJK
200B 01-May-17 LMN
200B 02-May-17 OPQ
300A 05-Jun-17 RST
;


proc sql noprint;
select max(n) into : n
 from (select count(*) as n from have group by account);
quit;
proc summary data=have ;
by account;
output out=want idgroup(out[&n] (dob surname)=);
run;