topic Re: re: Expand Records in SAS Programming

re: Expand Records

twildone — Thu, 07 Dec 2017 01:23:47 GMT

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

Re: re: Expand Records

Reeza — Thu, 07 Dec 2017 01:25:07 GMT

Sample data as a data step?

Re: re: Expand Records

PeterClemmensen — Thu, 07 Dec 2017 07:17:32 GMT

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;

Re: re: Expand Records

Ksharp — Thu, 07 Dec 2017 13:20:22 GMT

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;