re: expanding rows

[twildone]

Hi...I am trying to obtain each Season that is between two dates (STD & ENDT). The problem I am having is I want to have the Spring Season end on June 15 rather than June 30. I have tried the following:

end_subperiod=intnx('year.7',start_subperiod,0,'end',-15)

but that didn't work. Any suggestions.Thanks.

data Have;
  infile datalines dsd truncover;
  input STD:yymmdd10. ENDT:yymmdd10.;
  format std endt yymmdd10.;
datalines4;
2016-06-02,2016-09-24
2016-06-15,2016-09-26

2016-06-19,2016-09-30
;;;;

data want;
  set have;

  format start_subperiod end_subperiod yymmddn8.;

  array month_to_season_map {12} $6 _temporary_ 
           ('Winter','Winter','Winter'          
           ,'Spring','Spring','Spring'          
           ,'Summer','Summer'                   
           ,'Fall','Fall','Fall','Fall');       

  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) end_subperiod=intnx('year.1',start_subperiod,0,'end');
	  when (1,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;  
  end;
run;

Result:

STD	ENDT	start_subperiod	end_subperiod	seasonstart	seasonend	season
2016-06-02	2016-09-24	20160602	20160630	Spring	Fall	Spring
2016-06-02	2016-09-24	20160701	20160831	Spring	Fall	Summer
2016-06-02	2016-09-24	20160901	20160924	Spring	Fall	Fall
2016-06-15	2016-09-26	20160615	20160630	Spring	Fall	Spring
2016-06-15	2016-09-26	20160701	20160831	Spring	Fall	Summer
2016-06-15	2016-09-26	20160901	20160926	Spring	Fall	Fall
2016-06-19	2016-09-30	20160619	20160630	Spring	Fall	Spring
2016-06-19	2016-09-30	20160701	20160831	Spring	Fall	Summer
2016-06-19	2016-09-30	20160901	20160930	Spring	Fall	Fall
Want:
STD	ENDT	start_subperiod	end_subperiod	seasonstart	seasonend	season
2016-06-02	2016-09-24	20160602	20160615	Spring	Fall	Spring
2016-06-02	2016-09-24	20160616	20160831	Spring	Fall	Summer
2016-06-02	2016-09-24	20160901	20160924	Spring	Fall	Fall
2016-06-15	2016-09-26	20160615	20160615	Spring	Fall	Spring
2016-06-15	2016-09-26	20160616	20160831	Spring	Fall	Summer
2016-06-15	2016-09-26	20160901	20160926	Spring	Fall	Fall
2016-06-19	2016-09-30	20160619	20160831	Summer	Fall	Summer
2016-06-19	2016-09-30	20160901	20160930	Summer	Fall	Fall

[ballardw]

---

@twildone wrote:

Hi...I am trying to obtain each Season that is between two dates (STD & ENDT). The problem I am having is I want to have the Spring Season end on June 15 rather than June 30. I have tried the following:

end_subperiod=intnx('year.7',start_subperiod,0,'end',-15)

So what dates do other quarters end on? Start on? If you end Spring on June 15th doesn't that mean Summer is supposed to start on June 16th? Kind of dealing with an incomplete description.

And why use MONTH, which will not align with the stated objective to set a mid-month change of value?

I am also confused as to the purpose of Seasonstart and Seasonend. Do you actually want those??

How much of a time interval is this supposed to apply to? I would tend to make a custom format to do this.

[twildone]

Hi Ballardw....unfortunately Yes Summer is going to start on June 16 and Fall is going to start on August 16. I want to use the 15th as the cutoff because we will be having programs start toward the end of June (want these programs to be considered Summer as well) as well as toward the end of August.(want these to be considered Fall as well).

[FloydNevseta · Accepted solution]

A solution is to use a format with a range to convert a date into the proper season. First, create the format with a control data set.

data seasons;
stdt = '01jan2016'd; enddt = '31mar2016'd; season = 'Winter'; output;
stdt = '01apr2016'd; enddt = '15jun2016'd; season = 'Spring'; output;
stdt = '16jun2016'd; enddt = '15aug2016'd; season = 'Summer'; output;
stdt = '16aug2016'd; enddt = '31dec2016'd; season = 'Fall'; output;
run;

data fmt;
set seasons (rename=(stdt=start enddt=end season=label)) end=eof;
retain fmtname 'seasons' type 'n';
output;
if eof then do;
    hlo = 'O';
    label = 'Error';
    output;
end;

proc format cntlin=fmt;
run;

The format easily allows you to determine what season any day of the year occurs..

data want;
set have;

season_start = put(std, seasons.);
season_end = put(endt, seasons.);

format start_subper end_subper yymmdd10.;
start_subper = std;
season = season_start;
length _s $6;

* cycle through the date range to find seasonal subperiods;
do _st = std to endt;
    _s = put(_st, seasons.);

    * detect a change in season;
    if _s ne season then do;
        end_subper = _st - 1; 
        output;
        season = _s;
        start_subper = _st;
    end;
end;
end_subper = endt;
output;

drop _:;
run;

Result:

STD	ENDT	season_start	season_end	start_subper	end_subper	season
2016-06-02	2016-09-24	Spring	Fall	2016-06-02	2016-06-15	Spring
2016-06-02	2016-09-24	Spring	Fall	2016-06-16	2016-08-15	Summer
2016-06-02	2016-09-24	Spring	Fall	2016-08-16	2016-09-24	Fall
2016-06-15	2016-09-26	Spring	Fall	2016-06-15	2016-06-15	Spring
2016-06-15	2016-09-26	Spring	Fall	2016-06-16	2016-08-15	Summer
2016-06-15	2016-09-26	Spring	Fall	2016-08-16	2016-09-26	Fall
2016-06-19	2016-09-30	Summer	Fall	2016-06-19	2016-08-15	Summer
2016-06-19	2016-09-30	Summer	Fall	2016-08-16	2016-09-30	Fall
2016-08-10	2016-08-16	Summer	Fall	2016-08-10	2016-08-15	Summer
2016-08-10	2016-08-16	Summer	Fall	2016-08-16	2016-08-16	Fall

[Tom]

It is probably easier to use a FORMAT to calculate the SEASON.  

data have;
  id+1;
  input STD :yymmdd. ENDT :yymmdd.;
  format std endt yymmdd10.;
datalines4;
2016-06-02 2016-09-24
2016-06-15 2016-09-26
2016-06-19 2016-09-30
2016-01-15 2016-02-28
;;;;

proc format ;
value $season
  '0101'-'0315','1216'-'1231' = 'WINTER'
  '0316'-'0615' = 'SPRING'
  '0616'-'0815' = 'SUMMER'
  '0816'-'1215' = 'FALL'
;
run;

Then you can just loop over all of the days in the period and output a new record when the season changes.

data want;
  set have ;
  season_start = put(put(std,mmddyyn4.),$season.);
  season_end = put(put(endt,mmddyyn4.),$season.);
  subperiod_start = std ;
  season=season_start ;
  do date=std to endt while (season ne season_end);
    if put(put(date,mmddyyn4.),$season.) ne season then do;
      subperiod_end = date - 1;
      output;
      subperiod_start=date ;
      subperiod_end=.;
      season=put(put(date,mmddyyn4.),$season.);
    end;
  end;
  if missing(subperiod_end) then do;
     subperiod_end=endt ;
     output;
  end;
  format date subperiod_start subperiod_end yymmdd10.;
  drop date;
run;

Results