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
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;
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
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;
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;
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.