Hi....I have enrollment data which contains records of students in a program for the different Academic Years. I am trying to break the Academic Year records into terms where Term 1 is from the beginning of September to the end of the following January and Term 2 is from the beginning of February to the end of June. Any suggestions on how to do this.....thanks.
Have:
StudentID | Program | StartDate | CompletionDate |
150034 | Autobody | 20170902 | 20180630 |
150043 | AutoMechanics | 20170903 | 20180625 |
150054 | HairStyling | 20160905 | 20170628 |
150068 | HairStyling | 20170904 | 20180627 |
150075 | AutoMechanics | 20160903 | 20170626 |
150078 | AutoMechanics | 20170903 | 20180625 |
150088 | Autobody | 20170902 | 20180630 |
Want:
StudentID | Program | StartDate | CompletionDate |
150034 | Autobody | 20170902 | 20180131 |
150034 | Autobody | 20180201 | 20180630 |
150043 | AutoMechanics | 20170903 | 20180131 |
150043 | AutoMechanics | 20180201 | 20180625 |
150054 | HairStyling | 20160905 | 20170131 |
150054 | HairStyling | 20170201 | 20180628 |
150068 | HairStyling | 20170904 | 20180131 |
150068 | HairStyling | 20180201 | 20180627 |
150075 | AutoMechanics | 20160903 | 20170131 |
150075 | AutoMechanics | 20170201 | 20170626 |
150078 | AutoMechanics | 20170903 | 20180131 |
150078 | AutoMechanics | 20180201 | 20180625 |
150088 | Autobody | 20170902 | 20180131 |
150088 | Autobody | 20180201 | 20180630 |
data have;
length StudentID $6 Program $100;
input StudentID$ Program$ StartDate:yymmdd10. CompletionDate:yymmdd10.;
format StartDate CompletionDate yymmdd10.;
datalines;
150034 Autobody 20170902 20180630
150043 AutoMechanics 20170903 20180625
150054 HairStyling 20160905 20170628
150068 HairStyling 20170904 20180627
150075 AutoMechanics 20160903 20170626
150078 AutoMechanics 20170903 20180625
150088 Autobody 20170902 20180630
;
data temp;
set have;
temp=StartDate;
do date=StartDate to CompletionDate;
month=intck('month.',temp,date)+1;
output;
end;
format date date9. ;
drop StartDate CompletionDate temp;
run;
data temp1;
set temp;
by StudentID month notsorted;
if first.month and mod(month,5)=1 then group+1;
run;
data want;
set temp1;
by StudentID group notsorted;
retain start;
if first.group then start=date;
if last.group then do;end=date;output;end;
format start end yymmdd10.;
drop date group month;
run;
Sample data as a data step?
Something like this?
data have;
length StudentID $6 Program $100;
input StudentID$ Program$ StartDate:yymmdd10. CompletionDate:yymmdd10.;
format StartDate CompletionDate yymmdd10.;
datalines;
150034 Autobody 20170902 20180630
150043 AutoMechanics 20170903 20180625
150054 HairStyling 20160905 20170628
150068 HairStyling 20170904 20180627
150075 AutoMechanics 20160903 20170626
150078 AutoMechanics 20170903 20180625
150088 Autobody 20170902 20180630
;
data want;
set have;
do dt=StartDate to CompletionDate;
if dt=mdy(1, 31, year(StartDate)+1) then do;
CompletionDate_=dt;
output;
StartDate=dt+1;
end;
end;
CompletionDate_=CompletionDate;
output;
format CompletionDate_ mmddyy10.;
rename CompletionDate_=CompletionDate;
keep StudentID Program StartDate CompletionDate_;
run;
data have;
length StudentID $6 Program $100;
input StudentID$ Program$ StartDate:yymmdd10. CompletionDate:yymmdd10.;
format StartDate CompletionDate yymmdd10.;
datalines;
150034 Autobody 20170902 20180630
150043 AutoMechanics 20170903 20180625
150054 HairStyling 20160905 20170628
150068 HairStyling 20170904 20180627
150075 AutoMechanics 20160903 20170626
150078 AutoMechanics 20170903 20180625
150088 Autobody 20170902 20180630
;
data temp;
set have;
temp=StartDate;
do date=StartDate to CompletionDate;
month=intck('month.',temp,date)+1;
output;
end;
format date date9. ;
drop StartDate CompletionDate temp;
run;
data temp1;
set temp;
by StudentID month notsorted;
if first.month and mod(month,5)=1 then group+1;
run;
data want;
set temp1;
by StudentID group notsorted;
retain start;
if first.group then start=date;
if last.group then do;end=date;output;end;
format start end yymmdd10.;
drop date group month;
run;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.