topic Re: Can someone please help me with adding in date like in this case? in SAS Programming

Can someone please help me with adding in date like in this case?

LightJade — Mon, 11 Jan 2021 02:26:15 GMT

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!!

Re: Can someone please help me with adding in date like in this case?

novinosrin — Mon, 11 Jan 2021 03:10:54 GMT

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;

Re: Can someone please help me with adding in date like in this case?

Ksharp — Mon, 11 Jan 2021 11:57:10 GMT


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;