BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
PatrykSAS
Obsidian | Level 7

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
Kurt_Bremser
Super User

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

View solution in original post

10 REPLIES 10
PeterClemmensen
Tourmaline | Level 20

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

PatrykSAS
Obsidian | Level 7
I just didn't want to write down every possible case and listed the first few
Kurt_Bremser
Super User

@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
Obsidian | Level 7
Works perfectly. Thank you all for support. Sorry for the trouble with the problem description, I should've adjusted the sample set.
Kurt_Bremser
Super User

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
Obsidian | Level 7
Yes it is. This is just an example not to write out all the lines by hand month by month.
PaigeMiller
Diamond | Level 26

@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
PaigeMiller
Diamond | Level 26

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

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;

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 557 views
  • 0 likes
  • 4 in conversation