topic Re: Using Retain, Loop through and transpose in SAS Programming

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

anandrc — Thu, 21 Dec 2017 13:57:53 GMT

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

Re: Using Retain, Loop through and transpose

Reeza — Wed, 20 Dec 2017 17:21:56 GMT

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

Re: Using Retain, Loop through and transpose

novinosrin — Wed, 20 Dec 2017 18:15:22 GMT

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;

Re: Using Retain, Loop through and transpose

Ksharp — Thu, 21 Dec 2017 13:28:41 GMT


 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;