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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.