DATA Step, Macro, Functions and more

re: Expand Rows

Reply
Regular Contributor
Posts: 245

re: Expand Rows

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
;
Esteemed Advisor
Posts: 5,391

Re: re: Expand Rows

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;
PG
Regular Contributor
Posts: 245

Re: re: Expand Rows

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

Esteemed Advisor
Posts: 5,391

Re: re: Expand Rows

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

PG
Regular Contributor
Posts: 245

Re: re: Expand Rows

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.

Ask a Question
Discussion stats
  • 4 replies
  • 177 views
  • 0 likes
  • 2 in conversation