Hi....I am trying to expand the record if the EndDate carries over to another Season. The Season are Fall from September 1 thru to the following January 31, Winter from February 1 thru March 31, Spring from April 1 thru June 30 and Summer from July 1 thru August 31. I was able to calculate "EndD" as the last day of the Start of the Season. Not sure how to proceed from here....any suggestions. Thanks.
data Have;
infile datalines dsd truncover;
input STD:yymmdd10. ENDT:yymmdd10. menu:$4.;
datalines4;
2016-09-02,2017-06-24,IDEM
2016-10-08,2017-04-26,IDEM
2016-11-15,2017-05-30,IDEM
2016-12-29,2017-01-15,IDEM
2017-01-02,2017-06-27,IDEM
2016-02-08,2016-05-24,IDEM
2016-03-15,2016-10-26,IDEM
2016-04-29,2016-08-29,IDEM
2016-05-02,2016-11-23,IDEM
2016-06-08,2016-07-25,IDEM
2016-07-15,2016-08-27,IDEM
2016-08-29,2016-12-15,IDEM
;;;;
data new;
length SeasonStart SeasonEnd $6.;
format SeasonStart SeasonEnd $6.;
set Have;
if substr(put(STD,yymmdd10.),6,2) in ('09','10','11','12','01') then do;
StartDate = put(STD,yymmdd10.);
EndDate = put(ENDT,yymmdd10.);
EndD = put(intnx('year.2',STD,0,'e'),yymmdd10.);
SeasonStart = 'Fall';
end;
if substr(put(STD,yymmdd10.),6,2) in ('02') then do;
StartDate = put(STD,yymmdd10.);
EndDate = put(ENDT,yymmdd10.);
EndD = put(intnx('year.7',STD,0,'e'),yymmdd10.);
SeasonStart = 'Winter';
end;
if substr(put(STD,yymmdd10.),6,2) in ('03','04','05','06') then do;
StartDate = put(STD,yymmdd10.);
EndDate = put(ENDT,yymmdd10.);
EndD = put(intnx('year.7',STD,0,'e'),yymmdd10.);
SeasonStart = 'Spring';
end;
if substr(put(STD,yymmdd10.),6,2) in ('07','08') then do;
StartDate = put(STD,yymmdd10.);
EndDate = put(ENDT,yymmdd10.);
EndD = put(intnx('year.9',STD,0,'e'),yymmdd10.);
SeasonStart = 'Summer';
end;
if substr(put(ENDT,yymmdd10.),6,2) in ('09','10','11','12','01') then do;
SeasonEnd = 'Fall';
end;
if substr(put(ENDT,yymmdd10.),6,2) in ('02','05','06') then do;
SeasonEnd = 'Winter';
end;
if substr(put(ENDT,yymmdd10.),6,2) in ('03','04') then do;
SeasonEnd = 'Spring';
end;
if substr(put(ENDT,yymmdd10.),6,2) in ('07','08') then do;
SeasonEnd = 'Summer';
end;
run;
Want:
SeasonStart | SeasonEnd | STD | ENDT | menu | StartDate | EndDate | EndD | StartDate1 | EndDate1 | Season |
Fall | Winter | 20699 | 20994 | IDEM | 2016-09-02 | 2017-06-24 | 2017-01-31 | 2016-09-02 | 2017-01-31 | Fall |
Fall | Winter | 20699 | 20994 | IDEM | 2016-09-02 | 2017-06-24 | 2017-01-31 | 2017-02-01 | 2017-03-31 | Winter |
Fall | Winter | 20699 | 20994 | IDEM | 2016-09-02 | 2017-06-24 | 2017-01-31 | 2017-04-01 | 2017-06-24 | Spring |
Fall | Spring | 20735 | 20935 | IDEM | 2016-10-08 | 2017-04-26 | 2017-01-31 | 2016-10-08 | 2017-01-31 | Fall |
Fall | Spring | 20735 | 20935 | IDEM | 2016-10-08 | 2017-04-26 | 2017-01-31 | 2017-02-01 | 2017-03-31 | Winter |
Fall | Spring | 20735 | 20935 | IDEM | 2016-10-08 | 2017-04-26 | 2017-01-31 | 2017-04-01 | 2017-04-26 | Spring |
Fall | Winter | 20773 | 20969 | IDEM | 2016-11-15 | 2017-05-30 | 2017-01-31 | 2016-11-15 | 2017-01-31 | Fall |
Fall | Winter | 20773 | 20969 | IDEM | 2016-11-15 | 2017-05-30 | 2017-01-31 | 2017-02-01 | 2017-03-31 | Winter |
Fall | Winter | 20773 | 20969 | IDEM | 2016-11-15 | 2017-05-30 | 2017-01-31 | 2017-04-01 | 2017-05-30 | Spring |
Fall | Fall | 20817 | 20834 | IDEM | 2016-12-29 | 2017-01-15 | 2017-01-31 | 2016-12-29 | 2017-01-15 | Fall |
Fall | Winter | 20821 | 20997 | IDEM | 2017-01-02 | 2017-06-27 | 2017-01-31 | 2017-01-02 | 2017-01-31 | Fall |
Fall | Winter | 20821 | 20997 | IDEM | 2017-01-02 | 2017-06-27 | 2017-01-31 | 2017-02-01 | 2017-03-31 | Winter |
Fall | Winter | 20821 | 20997 | IDEM | 2017-01-02 | 2017-06-27 | 2017-01-31 | 2017-04-01 | 2017-06-27 | Spring |
Winter | Winter | 20492 | 20598 | IDEM | 2016-02-08 | 2016-05-24 | 2016-06-30 | 2016-02-08 | 2016-03-31 | Winter |
Winter | Winter | 20492 | 20598 | IDEM | 2016-02-08 | 2016-05-24 | 2016-06-30 | 2016-04-01 | 2016-05-24 | Spring |
Spring | Fall | 20528 | 20753 | IDEM | 2016-03-15 | 2016-10-26 | 2016-06-30 | 2016-03-15 | 2016-03-31 | Winter |
Spring | Fall | 20528 | 20753 | IDEM | 2016-03-15 | 2016-10-26 | 2016-06-30 | 2016-04-01 | 2016-06-30 | Spring |
Spring | Fall | 20528 | 20753 | IDEM | 2016-03-15 | 2016-10-26 | 2016-06-30 | 2016-07-01 | 2016-08-31 | Summer |
Spring | Fall | 20528 | 20753 | IDEM | 2016-03-15 | 2016-10-26 | 2016-06-30 | 2016-09-01 | 2016-10-26 | Fall |
Spring | Summer | 20573 | 20695 | IDEM | 2016-04-29 | 2016-08-29 | 2016-06-30 | 2016-04-29 | 2016-06-30 | Spring |
Spring | Summer | 20573 | 20695 | IDEM | 2016-04-29 | 2016-08-29 | 2016-06-30 | 2016-07-01 | 2016-08-29 | Summer |
Spring | Fall | 20576 | 20781 | IDEM | 2016-05-02 | 2016-11-23 | 2016-06-30 | 2016-05-02 | 2016-06-30 | Spring |
Spring | Fall | 20576 | 20781 | IDEM | 2016-05-02 | 2016-11-23 | 2016-06-30 | 2016-07-01 | 2016-08-31 | Summer |
Spring | Fall | 20576 | 20781 | IDEM | 2016-05-02 | 2016-11-23 | 2016-06-30 | 2016-09-01 | 2016-11-23 | Fall |
Spring | Summer | 20613 | 20660 | IDEM | 2016-06-08 | 2016-07-25 | 2016-06-30 | 2016-06-08 | 2016-06-30 | Spring |
Spring | Summer | 20613 | 20660 | IDEM | 2016-06-08 | 2016-07-25 | 2016-06-30 | 2016-07-01 | 2016-07-25 | Summer |
Summer | Summer | 20650 | 20693 | IDEM | 2016-07-15 | 2016-08-27 | 2016-08-31 | 2016-07-15 | 2016-08-27 | Summer |
Summer | Fall | 20695 | 20803 | IDEM | 2016-08-29 | 2016-12-15 | 2016-08-31 | 2016-08-29 | 2016-08-31 | Summer |
Summer | Fall | 20695 | 20803 | IDEM | 2016-08-29 | 2016-12-15 | 2016-08-31 | 2016-09-01 | 2016-12-15 | Fall |
This is where
data Have;
infile datalines dsd truncover;
input STD:yymmdd10. ENDT:yymmdd10. menu:$4.;
format std endt yymmddn8.;
datalines4;
2016-09-02,2017-06-24,IDEM
2016-10-08,2017-04-26,IDEM
2016-11-15,2017-05-30,IDEM
2016-12-29,2017-01-15,IDEM
2017-01-02,2017-06-27,IDEM
2016-02-08,2016-05-24,IDEM
2016-03-15,2016-10-26,IDEM
2016-04-29,2016-08-29,IDEM
2016-05-02,2016-11-23,IDEM
2016-06-08,2016-07-25,IDEM
2016-07-15,2016-08-27,IDEM
2016-08-29,2016-12-15,IDEM
;;;;
data want;
set have;
format start_subperiod end_subperiod yymmddn8.;
array month_to_season_map {12} $6 _temporary_
('FALL' /* Jan */
,'WINTER','WINTER' /* Feb, Mar */
,'SPRING','SPRING','SPRING' /* Apr May Jun */
,'SUMMER','SUMMER' /* Jul Aug */
,'FALL','FALL','FALL','FALL'); /* Sep Oct Nov Dec */
seasonstart=month_to_season_map{month(std)};
seasonend=month_to_season_map{month(endt)};
start_subperiod=std;
do while (start_subperiod<=endt);
select(month(start_subperiod)); /* Align end_subperiod */
when (9,10,11,12,1) end_subperiod=intnx('year.2',start_subperiod,0,'end');
when (2,3) end_subperiod=intnx('year.4',start_subperiod,0,'end');
when (4,5,6) end_subperiod=intnx('year.7',start_subperiod,0,'end');
when (7,8) end_subperiod=intnx('year.9',start_subperiod,0,'end');
end;
end_subperiod=min(end_subperiod,endt);
season=month_to_season_map{month(end_subperiod)};
output;
start_subperiod=end_subperiod+1; /* go to next season */
end;
run;
This is where
data Have;
infile datalines dsd truncover;
input STD:yymmdd10. ENDT:yymmdd10. menu:$4.;
format std endt yymmddn8.;
datalines4;
2016-09-02,2017-06-24,IDEM
2016-10-08,2017-04-26,IDEM
2016-11-15,2017-05-30,IDEM
2016-12-29,2017-01-15,IDEM
2017-01-02,2017-06-27,IDEM
2016-02-08,2016-05-24,IDEM
2016-03-15,2016-10-26,IDEM
2016-04-29,2016-08-29,IDEM
2016-05-02,2016-11-23,IDEM
2016-06-08,2016-07-25,IDEM
2016-07-15,2016-08-27,IDEM
2016-08-29,2016-12-15,IDEM
;;;;
data want;
set have;
format start_subperiod end_subperiod yymmddn8.;
array month_to_season_map {12} $6 _temporary_
('FALL' /* Jan */
,'WINTER','WINTER' /* Feb, Mar */
,'SPRING','SPRING','SPRING' /* Apr May Jun */
,'SUMMER','SUMMER' /* Jul Aug */
,'FALL','FALL','FALL','FALL'); /* Sep Oct Nov Dec */
seasonstart=month_to_season_map{month(std)};
seasonend=month_to_season_map{month(endt)};
start_subperiod=std;
do while (start_subperiod<=endt);
select(month(start_subperiod)); /* Align end_subperiod */
when (9,10,11,12,1) end_subperiod=intnx('year.2',start_subperiod,0,'end');
when (2,3) end_subperiod=intnx('year.4',start_subperiod,0,'end');
when (4,5,6) end_subperiod=intnx('year.7',start_subperiod,0,'end');
when (7,8) end_subperiod=intnx('year.9',start_subperiod,0,'end');
end;
end_subperiod=min(end_subperiod,endt);
season=month_to_season_map{month(end_subperiod)};
output;
start_subperiod=end_subperiod+1; /* go to next season */
end;
run;
Thanks mkeintz...works perfectly!!
This method uses a custom interval data set and makes the uneven intervals pretty easy to work with. It should match the result from @mkeintz.
options intervalds=(XSEASON=XSEAS);
data xseas(keep=begin season);
do y = 2015 to 2017;
do m=2,4,7,9;
begin = mdy(m,1,y);
season = whichn(m,2,4,7,9);
output;
end;
end;
format begin date9.;
run;
proc print;
run;
data Have;
id + 1;
infile datalines dsd truncover;
input STD:yymmdd10. ENDT:yymmdd10. menu:$4.;
format std endt yymmdd10.;
datalines4;
2016-09-02,2017-06-24,IDEM
2016-10-08,2017-04-26,IDEM
2016-11-15,2017-05-30,IDEM
2016-12-29,2017-01-15,IDEM
2017-01-02,2017-06-27,IDEM
2016-02-08,2016-05-24,IDEM
2016-03-15,2016-10-26,IDEM
2016-04-29,2016-08-29,IDEM
2016-05-02,2016-11-23,IDEM
2016-06-08,2016-07-25,IDEM
2016-07-15,2016-08-27,IDEM
2016-08-29,2016-12-15,IDEM
;;;;
run;
proc print;
run;
data want;
set have;
std0 = std;
do i = 0 to intck('XSEASON',std,endt);
xstd = max(intnx('XSEASON',std0,0,'B'),std);
xend = min(intnx('XSEASON',std0,0,'E'),endt);
s = INTINDEX('XSEASON',xstd);
length season $8;
season = choosec(s,'WINTER','SPRING','SUMMER','FALL');
output;
std0 = xend+1;
end;
format x: std0 yymmdd10.;
run;
proc print;
by id;
id id;
run;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.