How to Obtain One Observation from Multiple Observations

[analyst11]

Hello,

New SAS user here. I have a dataset with ID, date, and different diagnoses given on that date. I would like to transform this dataset from long to wide as seen in the example below. But I am having trouble using do-loop.

The first table is the dataset I have. The second table is what I want. Any help would be appreciated.  Thank you

ID	Date	Diagnosis1	Diagnosis2	Diagnosis3	Diagnosis4	Diagnosis5
1	1/20/19	F123	F124
1	1/21/19	F284	F124	F3
1	1/22/19	F156
1	1/23/19	F189
2	2/1/19	F98
2	2/2/19	F78
3	3/1/19	F56
3	3/2/19	F57
3	3/3/19	F10	F11	F12	F13	F14
1	3/20/19	F17
3	9/1/19	F124

ID

Date1

Diagnosis1

Date2

Diagnosis2

Date3

Diagnosis3

Date4

Diagnosis4

Date5

Diagnosis5

Date6

Diagnosis6

Date7

Diagnosis7

Date8

Diagnosis8

1

1/20/19

F123

1/20/19

F124

1/21/19

F284

1/21/19

F124

1/21/19

F3

1/22/19

F156

1/23/19

F189

3/20/19

F17

2

2/1/19

F98

2/2/19

F78

3

3/1/19

F56

3/2/19

F57

3/3/19

F10

3/3/19

F11

3/3/19

F12

3/3/19

F13

3/3/19

F14

9/1/19

F124

[Reeza]

Why do you want that? In general, it's much much easier to work with your data in SAS (and other languages) in a long format than a wide format. The wide format is conventional in SPSS and for doing statistical analysis, but not as useful for reporting or calculating overall metrics.

[ballardw]

Please describe exactly what you are going to do with that wide format data. It is sometimes awkward enough to deal with multiple related variables on a single date but by the time you get all of those dates mixed in, it gets much harder to work with.

[analyst11]

The goal is to display dates and diagnoses by observation in a table format. This is the preferred method for the client. 

[Reeza]

There's a macro on here (you can use the search feature above) called "A better way to flip" that will do this for you.

You may need to restructure your data set to a long overall format first though.

[Ksharp · Accepted solution]

Check my paper - Merge Skill:

http://support.sas.com/resources/papers/proceedings15/2785-2015.pdf

data have;
infile cards expandtabs truncover;
input ID	(Date	Diagnosis1	Diagnosis2	Diagnosis3	Diagnosis4	Diagnosis5) ($);
cards;
1	1/20/19	F123	F124	 	 	 
1	1/21/19	F284	F124	F3	 	 
1	1/22/19	F156	 	 	 	 
1	1/23/19	F189	 	 	 	 
2	2/1/19	F98	 	 	 	 
2	2/2/19	F78	 	 	 	 
3	3/1/19	F56	 	 	 	 
3	3/2/19	F57	 	 	 	 
3	3/3/19	F10	F11	F12	F13	F14
1	3/20/19	F17	 	 	 	 
3	9/1/19	F124	 	
;
run;
proc transpose data=have out=temp(where=(col1 is not missing));
by id date notsorted;
var diag: ;
run; 
data temp1;
 set temp;
 by id;
 if first.id then n=0;
 n+1;
run;
proc freq data=temp1 noprint;
table n/out=n list ;
run;

data _null_;
 set n end=last;
 if _n_=1 then call execute('data want;merge ');
 call execute(catt('temp1(where=(n=',n,') rename=(date=date',n,' col1=diag',n,'))'));
 if last then call execute(';by id;drop _name_ n ;run;');
run;