DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 7
Accepted Solution

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

[ Edited ]

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


Accepted Solutions
Solution
‎12-21-2017 04:21 AM
PROC Star
Posts: 1,351

Re: Using Retain, Loop through and transpose

[ Edited ]

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;

 

 

View solution in original post


All Replies
Super User
Posts: 22,874

Re: Using Retain, Loop through and transpose

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

Solution
‎12-21-2017 04:21 AM
PROC Star
Posts: 1,351

Re: Using Retain, Loop through and transpose

[ Edited ]

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;

 

 

Super User
Posts: 10,621

Re: Using Retain, Loop through and transpose


 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;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 107 views
  • 3 likes
  • 4 in conversation