DATA Step, Macro, Functions and more

re: Expand Records

Accepted Solution Solved
Reply
Regular Contributor
Posts: 245
Accepted Solution

re: Expand Records

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

Accepted Solutions
Solution
‎12-09-2017 11:22 AM
Super User
Posts: 10,610

Re: re: Expand Records

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;

View solution in original post


All Replies
Super User
Posts: 22,823

Re: re: Expand Records

Sample data as a data step?

PROC Star
Posts: 1,190

Re: re: Expand Records

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;
Solution
‎12-09-2017 11:22 AM
Super User
Posts: 10,610

Re: re: Expand Records

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;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 152 views
  • 0 likes
  • 4 in conversation