Spreading the dates over the months

[PatrykSAS]

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?

[PeterClemmensen]

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

[PatrykSAS]

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

[Kurt_Bremser · Accepted solution]

---

@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;

[PatrykSAS]

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

[Kurt_Bremser]

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).

[PatrykSAS]

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

[PaigeMiller]

---

@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.

[Kurt_Bremser]

---

@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.

[PaigeMiller]

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;

[PeterClemmensen]

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;