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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.