topic Re: Widen dataset sas9.4 in SAS Programming

Widen dataset sas9.4

Whitlea — Fri, 03 May 2024 23:54:49 GMT

I need suggestions on how to widen my dataset. I need a single line for each ID while keeping all the data in the sample dataset. I plan on later merging this with another dataset by ID. I was unable to transpose the data because an ID can have multiple same_spans of the same number (see ID R in the data example).

Thanks!

Data Have;
input 

ID$	Clinic$ Span_Beg :Date9. Span_End :Date9.  Same_Span ;
format Span_Beg MMDDYY10. Span_End MMDDYY10.;
datalines;
A	Clinic1	 	17Nov2022	4Jan2023		1	
B	Clinic2	 	1Jul2022	29Jul2022		1	
B	Clinic2	 	21Nov2022	4Apr2023		2	
C	Clinic1	 	1Jul2022	7Jul2022		1	
D	Clinic3	 	1Jul2022	12Jul2022		1	
E	Clinic1	 	22Nov2022	19Mar2023		1	
F	Clinic2	 	26Aug2022	15May2023		1	
G	Clinic4	 	1Oct2022	10Apr2023		1	
H	Clinic5	 	30Dec2022	19Jan2023		1	
I	Clinic6	 	1Jul2022	1Nov2022		1	
I	Clinic6	 	11Nov2022	10Jan2023		2	
I	Clinic6	 	1Feb2023	30Apr2023		3	
J	Clinic2	 	1Jul2022	21Aug2022		1	
K	Clinic2	 	9Mar2023	30Jun2023		1	
L	Clinic5	 	20Sep2022	20Feb2023		1	
M	Clinic5	 	1Jul2022	2Aug2022		1	
N	Clinic1	 	6Jul2022	1Sep2022		1	
O	Clinic7	 	1Jul2022	1Sep2022		1	
P	Clinic8	 	1Jul2022	24Oct2022		1	
Q	Clinic8	 	1Jul2022	4Dec2022		1	
R	Clinic9	 	19Dec2022	31May2023		1	
R	Clinic9	 	8Jun2023	18Jun2023		2	
R	Clinic1	 	28Aug2022	18Sep2022		1	
S	Clinic1	 	1Jul2022	10Jul2022		1	
S	Clinic3	 	4Aug2022	17Nov2022		1	

;
run;

Re: Widen dataset sas9.4

Ksharp — Sat, 04 May 2024 01:05:58 GMT

The most simple way is using PROC SUMMARY.

Data Have;
input 
ID$	Clinic$ Span_Beg :Date9. Span_End :Date9.  Same_Span ;
format Span_Beg MMDDYY10. Span_End MMDDYY10.;
datalines;
A	Clinic1	 	17Nov2022	4Jan2023		1	
B	Clinic2	 	1Jul2022	29Jul2022		1	
B	Clinic2	 	21Nov2022	4Apr2023		2	
C	Clinic1	 	1Jul2022	7Jul2022		1	
D	Clinic3	 	1Jul2022	12Jul2022		1	
E	Clinic1	 	22Nov2022	19Mar2023		1	
F	Clinic2	 	26Aug2022	15May2023		1	
G	Clinic4	 	1Oct2022	10Apr2023		1	
H	Clinic5	 	30Dec2022	19Jan2023		1	
I	Clinic6	 	1Jul2022	1Nov2022		1	
I	Clinic6	 	11Nov2022	10Jan2023		2	
I	Clinic6	 	1Feb2023	30Apr2023		3	
J	Clinic2	 	1Jul2022	21Aug2022		1	
K	Clinic2	 	9Mar2023	30Jun2023		1	
L	Clinic5	 	20Sep2022	20Feb2023		1	
M	Clinic5	 	1Jul2022	2Aug2022		1	
N	Clinic1	 	6Jul2022	1Sep2022		1	
O	Clinic7	 	1Jul2022	1Sep2022		1	
P	Clinic8	 	1Jul2022	24Oct2022		1	
Q	Clinic8	 	1Jul2022	4Dec2022		1	
R	Clinic9	 	19Dec2022	31May2023		1	
R	Clinic9	 	8Jun2023	18Jun2023		2	
R	Clinic1	 	28Aug2022	18Sep2022		1	
S	Clinic1	 	1Jul2022	10Jul2022		1	
S	Clinic3	 	4Aug2022	17Nov2022		1	
;
run;

proc sql noprint;
select max(n) into :n
 from (select id,count(*) as n from have group by id);
quit;
proc summary data=have nway;
class id;
output out=want idgroup( out[&n.] (Clinic Span_Beg Span_End Same_Span)=);
run;

Re: Widen dataset sas9.4

Whitlea — Sat, 04 May 2024 02:21:42 GMT

This gives me exactly what I need. Thank you!!