topic Re: Spreading the dates over the months in SAS Programming

Spreading the dates over the months

PatrykSAS — Wed, 16 Feb 2022 14:04:26 GMT

Hi.

I want to designate each separate month between two dates. I want to create a row with the year and month between the start and end dates. The maximum I want to create a schedule by 2026, even though the end date may be greater.

data have;
   input id $ start :yymmdd10. stop :yymmdd10.;
   format start yymmddd10.;
   format stop yymmddd10.;
   datalines;
123 2021-02-01 2021-12-31
123 2022-01-01 2022-05-31
123 2022-06-01 9999-12-31
;

data want;
   input id $ start :yymmdd10. stop :yymmdd10. year month;
   format start yymmddd10.;
   format stop yymmddd10.;
   datalines;
123 2021-02-01 2021-12-31 2021 2
123 2021-02-01 2021-12-31 2021 3
123 2021-02-01 2021-12-31 2021 4
123 2022-01-01 2022-05-31 2022 1
123 2022-01-01 2022-05-31 2022 2
123 2022-01-01 2022-05-31 2022 3
123 2022-06-01 9999-12-31 2022 6
123 2022-06-01 9999-12-31 2022 7
123 2022-06-01 9999-12-31 2022 8
;

Data Want is just example, I didn't write out all the lines month by month. Can You help me with solution?

Re: Spreading the dates over the months

PeterClemmensen — Wed, 16 Feb 2022 13:45:26 GMT

Why does the last row only become 3 obs in the new data set?

Re: Spreading the dates over the months

PatrykSAS — Wed, 16 Feb 2022 13:51:56 GMT

I just didn't want to write down every possible case and listed the first few

Re: Spreading the dates over the months

Kurt_Bremser — Wed, 16 Feb 2022 13:55:03 GMT

Your WANT dataset seems to be incomplete. For the first row in HAVE, I would expect 11 rows in WANT (2,3,4,5,6,7,8,9,10,11,12), for the second 5 rows, and for the third 55 (7 in 2022 and 4*12 until 2026).

Re: Spreading the dates over the months

Kurt_Bremser — Wed, 16 Feb 2022 14:03:26 GMT

@PatrykSAS wrote:
I just didn't want to write down every possible case and listed the first few

That's not good. It causes us to unnecessarily have doubts about your question, delaying the solution. ALWAYS post complete WANT for your HAVE. ALWAYS.

See this code:

data want;
set have;
dt = start;
do until (dt > min(stop,'31dec2026'd));
  year = year(dt);
  month = month(dt);
  output;
  dt = intnx('month',dt,1,"e");
end;
drop dt;
run;

Re: Spreading the dates over the months

PatrykSAS — Wed, 16 Feb 2022 14:03:30 GMT

Yes it is. This is just an example not to write out all the lines by hand month by month.

Re: Spreading the dates over the months

PaigeMiller — Wed, 16 Feb 2022 14:10:20 GMT

The words of your description doesn't seem to match your output or I have misunderstood.

Your first row should produce all months in 2021 up to month 12, but your output doesn't show that. Please explain.

This code matches your description, but doesn't match your output.

data want;
    set have;
    if stop>'31DEC2026'd then stop='31DEC2026'd;
    do i = 0 to intck('month',start,stop);
        year=year(intnx('month',start,i,'b'));
        month=month(intnx('month',start,i,'b'));
        output;
    end;
    drop i;
run;

Re: Spreading the dates over the months

PaigeMiller — Wed, 16 Feb 2022 14:04:33 GMT

@PatrykSAS wrote:
Yes it is. This is just an example not to write out all the lines by hand month by month.

This needs to be stated in the original problem description.

Re: Spreading the dates over the months

Kurt_Bremser — Wed, 16 Feb 2022 14:04:37 GMT

@PatrykSAS wrote:
Yes it is. This is just an example not to write out all the lines by hand month by month.

Then you should have adapted the HAVE accordingly.

Re: Spreading the dates over the months

PeterClemmensen — Wed, 16 Feb 2022 14:06:02 GMT

I changed your input data a bit. See if this works for you

data have;
   input id $ start :yymmdd10. stop :yymmdd10.;
   format start yymmddd10.;
   format stop yymmddd10.;
   datalines;
123 2021-02-01 2021-12-31
123 2022-01-01 2022-05-31
123 2022-06-01 2022-12-31
;

data want(drop = i dt);
   set have;
   
   do i = 0 by 1 while (1);
      dt = intnx('month', start, i, 'b');
	  if dt > stop then leave;

	  year = year(dt);
      month = month(dt);
	  output;
	  
   end;
   format dt yymmdd10.;
run;

Re: Spreading the dates over the months

PatrykSAS — Wed, 16 Feb 2022 14:48:19 GMT

Works perfectly. Thank you all for support. Sorry for the trouble with the problem description, I should've adjusted the sample set.