Obsidian | Level 7

## Spreading the dates over the months

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Spreading the dates over the months

@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;``````
10 REPLIES 10
Tourmaline | Level 20

## Re: Spreading the dates over the months

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

Obsidian | Level 7

## Re: Spreading the dates over the months

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

## Re: Spreading the dates over the months

@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;``````
Obsidian | Level 7

## Re: Spreading the dates over the months

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

## Re: Spreading the dates over the months

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

Obsidian | Level 7

## Re: Spreading the dates over the months

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

## Re: Spreading the dates over the months

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

--
Paige Miller
Super User

## Re: Spreading the dates over the months

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

Diamond | Level 26

## Re: Spreading the dates over the months

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.

``````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;``````
--
Paige Miller
Tourmaline | Level 20

## Re: Spreading the dates over the months

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;``````
Discussion stats
• 10 replies
• 557 views
• 0 likes
• 4 in conversation