Solved: Re: re: Expand Records

twildone · Posted 10-09-2018 12:13 PM

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

mkeintz · Posted 10-09-2018 01:10 PM

This is where

character arrays mapping month number to text (edit: was 'test') values for seasons are compact and handy,
and
using the INTNX function with arguments like "year.2" (12-month years beginning with Feburary), "year.4" (12-month years beginning with April) etc. are the way to step through seasonal time spans:

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;

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

View solution in original post

mkeintz · Posted 10-09-2018 01:10 PM

This is where

character arrays mapping month number to text (edit: was 'test') values for seasons are compact and handy,
and
using the INTNX function with arguments like "year.2" (12-month years beginning with Feburary), "year.4" (12-month years beginning with April) etc. are the way to step through seasonal time spans:

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;

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

twildone · Posted 10-09-2018 01:35 PM

Thanks mkeintz...works perfectly!!

data_null__ · Posted 10-09-2018 03:30 PM

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;

re: Expand Records

Re: re: Expand Records

Re: re: Expand Records

Re: re: Expand Records

Re: re: Expand Records

SAS Innovate 2025: Register Now

SAS Training: Just a Click Away