DATA Step, Macro, Functions and more

Month-Year Loop

Accepted Solution Solved
Reply
Contributor
Posts: 64
Accepted Solution

Month-Year Loop

I have the following dataset:

 

Month

1

2

3

4

5

6

7

8

9

10

11

12

....

till 48.

 

I want to create a variable which appends 2012 from 1 to 12, then 2013 for 12-24 and 2014 from 24-48 and so on as follows. How to do this?

 

month-year

1-2012

...

12-2012

 

1-2013

....

12-2013

 


Accepted Solutions
Solution
‎08-10-2016 03:45 PM
Super User
Posts: 11,343

Re: Month-Year Loop

Posted in reply to sasmaverick

How about

data want;
   set have;
   date = intnx('month','1Dec2011'd,month);
   format date mmyyD7.;
run;

The advantage with a date variable, which would be the first day of any given month, is that you can calculate a lot of things with date related functions and change display by use of formats without having to create new variables constantly.

 

View solution in original post


All Replies
Super User
Posts: 19,770

Re: Month-Year Loop

Posted in reply to sasmaverick

I would recommend a SAS date. 

 

Do year=2013 to 2016;
Do month=1 to 12;
Date=mdy(month, 1, year);
Output;
End;
End;

Format date monyy7.;
Contributor
Posts: 64

Re: Month-Year Loop

Hi Reeza,

 

I tried this, but I am getting 2304 observation when in fact I should only get 48.

 

Regards,

 

Super User
Super User
Posts: 7,942

Re: Month-Year Loop

[ Edited ]
Posted in reply to sasmaverick

Your doing that logic for each row in your original dataset then aren't you.  @Reeza's post showed you how to get your output without needing that other data.  I would also agree, use a date structure where dates are needed.  Also, why do you want a big long list on month-year data?

Super User
Posts: 7,762

Re: Month-Year Loop

Posted in reply to sasmaverick

sasmaverick wrote:

Hi Reeza,

 

I tried this, but I am getting 2304 observation when in fact I should only get 48.

 

Regards,

 


Then you must have made a mistake when you copied the code. @Reeza's code cannot have more than 48 iterations of the inner loop.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super Contributor
Posts: 298

Re: Month-Year Loop

Posted in reply to sasmaverick

data have;
do m = 1 to 48;
output;
end;
run;

data want;
set have;

if 1 <= _N_ <= 12 then new = catx('-',m,'2012');
else if 13 <= _N_ <= 24 then new = catx('-',m-12,'2013');
else if 25 <= _N_ <= 36 then new = catx('-',m-24,'2014');
else if 37 <= _N_ <= 48 then new = catx('-',m-36,'2015');
run;
proc print data = want(obs=30);
run;

Contributor
Posts: 64

Re: Month-Year Loop

What I am saying is I have only month data in the dataset (1-48). For the first 12 months, I want the date variable as 1-2012, 2-2012 till 12-2012. For the next 12, I need date as 1-2013, 2-2013 and so on.... I dont want the date as 48-2015, Instead I want it as 12-2015

Super User
Posts: 7,762

Re: Month-Year Loop

Posted in reply to sasmaverick

@Reeza's code does not need that simple dataset 1 to 48 at all. Instead it creates the list of months on its own.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 5,497

Re: Month-Year Loop

Posted in reply to sasmaverick

How about:

 

year = 2011 + ceil(month/12);

 

If you want to put them together:

 

month_year = put(month, z2.) || '-' || put(year, 4.);

 

I added the leading zero for months, so they will sort properly and line up properly when printing.

Contributor
Posts: 64

Re: Month-Year Loop

Posted in reply to sasmaverick

The question I am asking is I have the following dataset:

Month

1

2

3

4

5

6

7

8

9

10

11

12

...till 48

 

Expected Output as follows:

Month Year 

1        2012

2       2012

3      2012

4      2012

5      2012

6      2012

7     2012

8     2012

9    2012

10    2012

11     2012

12    2012

1    2013

2   2013

3   2013

4  2013

5 and so on.....till 12

 

Super User
Posts: 5,497

Re: Month-Year Loop

Posted in reply to sasmaverick

Ah, yes.  Looks like we'll need a new variable as well:

 

mon = mod(month, 12);

if mon=0 then mon=1;

 

Then use MON instead of MONTH when constructing the combination variable.

 

That's just one way ... there should be many ways to create MON.

Contributor
Posts: 64

Re: Month-Year Loop

Posted in reply to Astounding

Yours alongwith Ballardw's suggestion helped me.

 

Thanks

Solution
‎08-10-2016 03:45 PM
Super User
Posts: 11,343

Re: Month-Year Loop

Posted in reply to sasmaverick

How about

data want;
   set have;
   date = intnx('month','1Dec2011'd,month);
   format date mmyyD7.;
run;

The advantage with a date variable, which would be the first day of any given month, is that you can calculate a lot of things with date related functions and change display by use of formats without having to create new variables constantly.

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 12 replies
  • 635 views
  • 4 likes
  • 7 in conversation