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;
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;
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;
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
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.
Ready to level-up your skills? Choose your own adventure.