Hi! May I know if someone can help me adding in dates to Table 1 below and output Table 2?
Table 1 | |||
StudentID | Date_from | Date_to | Sports |
100 | 1/1/2019 | 2/28/2019 | baseball |
100 | 3/1/2019 | 3/31/2019 | swim |
101 | 2/1/20219 | 2/28/2019 | football |
101 | 3/1/2019 | 4/30/2019 | tennis |
Table 2 | |||
StudentID | Date_from | Date_to | Sports |
100 | 1/1/2019 | 1/31/2019 | baseball |
100 | 2/1/2019 | 2/28/2019 | baseball |
100 | 3/1/2019 | 3/31/2019 | swim |
101 | 2/1/20219 | 2/28/2019 | football |
101 | 3/1/2019 | 3/31/2019 | tennis |
101 | 4/1/2019 | 4/30/2019 | tennis |
Basically I need to have each month for each student ID like in table 2 with sports filled in correctly for each month.
Greatly appreciate if someone can teach me how to realize this in SAS!
Thank you!!
Hi @LightJade It's an interative date increment-
data have;
input StudentID (Date_from Date_to) (:mmddyy10.) Sports :$12.;
format Date_from Date_to mmddyy10.;
cards;
100 1/1/2019 2/28/2019 baseball
100 3/1/2019 3/31/2019 swim
101 2/1/2019 2/28/2019 football
101 3/1/2019 4/30/2019 tennis
;
data want;
set have;
_d=date_to;
do while(Date_from<_d);
date_to=intnx('mon',date_from,0,'e');
output;
date_from=date_to+1;
end;
drop _d;
run;
data have;
input StudentID (Date_from Date_to) (:mmddyy10.) Sports :$12.;
format Date_from Date_to mmddyy10.;
cards;
100 1/1/2019 2/28/2019 baseball
100 3/1/2019 3/31/2019 swim
101 2/1/2019 2/28/2019 football
101 3/1/2019 4/30/2019 tennis
;
data temp;
set have;
do date=Date_from to Date_to;
output;
end;
format date mmddyy10.;
drop Date_from Date_to;
run;
proc sort data=temp;
by StudentID date;
run;
data temp2;
merge temp temp(keep=StudentID date rename=(StudentID=_StudentID date=_date) firstobs=2);
output;
if StudentID=_StudentID then do;
do date=date+1 to _date-1;
output;
end;
end;
drop _: ;
run;
data temp3;
set temp2;
group=intnx('month',date,0);
format group mmddyy10.;
run;
proc sql;
create table want as
select StudentID ,min(date) as from_date format=mmddyy10.,
max(date) as to_date format=mmddyy10.,max(sports) as sports
from temp3
group by StudentID ,group;
quit;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: