topic Re: re: Expand Rows in SAS Programming

re: Expand Rows

twildone — Fri, 15 Dec 2017 00:11:10 GMT

Hi.....I am trying to identify terms between a StartDate and CompletionDate. The terms are identified as July1 thru January 31 (Term 1), February 1 thru June 30 (Term 2), and March 1 thru June 30 (Term 3). Term 2 and Term 3 overlap from March 1 thru June 30 which depends on the StartDate. The StartDate determines the first term whereas the CompletionDate determines whether or not a new row is to be inserted with the next term. Any suggestions on how to do this...thanks

Want:

StudentID	Program	StartDate	CompletionDate	Term
150034	Autobody	20170702	20180131	Term 1
150034	Autobody	20180201	20180630	Term 2
150043	AutoMechanics	20170803	20180131	Term 1
150043	AutoMechanics	20180201	20180625	Term 2
150054	HairStyling	20170105	20170628	Term 2
150068	HairStyling	20180304	20180627	Term 3
150075	AutoMechanics	20160903	20170131	Term 1
150075	AutoMechanics	20170201	20170626	Term 2
150078	AutoMechanics	20180403	20180625	Term 3
150088	Autobody	20170902	20180130	Term 1

data have;
length StudentID $6 Program $100;
input StudentID$ Program$ StartDate:yymmdd10. CompletionDate:yymmdd10.;
format StartDate CompletionDate yymmdd10.;
datalines;
150034	Autobody 20170702 20180630
150043	AutoMechanics	20170803 20180625
150054	HairStyling 20170105 20170628
150068	HairStyling 20170304 20180627
150075	AutoMechanics	20160903 20170626
150078	AutoMechanics	20180403 20180625
150088	Autobody 20170902 20190125
150099	AutoMechanics	20180304 20180625
;

Re: re: Expand Rows

PGStats — Fri, 15 Dec 2017 05:51:21 GMT

I think this does what you want


data want;
set have;
sMonth = intck("month",intnx("year.7",startDate,0), startDate);
cMonth = intck("month",intnx("year.7",completionDate,0), completionDate);
if sMonth < 7 then sTerm=1;
else if sMonth < 8 then sTerm = 2;
else sTerm = 3; 
if cMonth < 7 then cTerm=1;
else cTerm = 2;
if sTerm=1 and cTerm=2 then do;
    term = "term 1";
    sDate = startDate;
    cDate = intnx("month",intnx("year.7",startDate,0),6,"end");
    output;
    term = "term 2";
    sDate = intnx("month",intnx("year.7",startDate,0),7); 
    cDate = completionDate;
    output;
    end;
else do;
    term = catx(" ", "term", sTerm);
    sDate = startDate;
    cDate = completionDate;
    output;
    end;
format sDate cDate yymmdd10.;
drop startDate completionDate;
rename sDate=startDate cDate=completionDate;
keep StudentID Program sDate cDate term;
run;

Re: re: Expand Rows

twildone — Fri, 15 Dec 2017 14:20:10 GMT

Hi PG....Thanks for your help.....it almost does exactly what I need and want....it doesn't advance to the next academic year if the completion date is in another academic year. For example:

150068 HairStyling 20170304 20180627

should end up with:

150068 HairStyling 20170304 20170630 Term 3

150068 HairStyling 20170701 20180131 Term 1

150068 HairStyling 20180201 20180627 Term 2

Re: re: Expand Rows

PGStats — Fri, 15 Dec 2017 19:18:35 GMT

You never mentioned anything about a program lasting more than two terms. So now a program can last three terms. Then what?

Re: re: Expand Rows

twildone — Fri, 15 Dec 2017 20:38:36 GMT

The duration of a program varies and have a specific start and completion date for that program. Not all programs start at the same time and can carry over to the next academic year. For example, a program may start in September and finish the following June where both start dates and completion dates are in the same academic year whereas the same program may be offered as January start and finish in December where the start dates and completion dates are in different academic years.