topic Re: Month-Year Loop in SAS Programming

Month-Year Loop

sasmaverick — Wed, 10 Aug 2016 12:04:25 GMT

I have the following dataset:

Month

....

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

Re: Month-Year Loop

Reeza — Wed, 10 Aug 2016 12:20:55 GMT

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

Re: Month-Year Loop

sasmaverick — Wed, 10 Aug 2016 12:27:13 GMT

Hi Reeza,

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

Regards,

Re: Month-Year Loop

KachiM — Wed, 10 Aug 2016 12:29:15 GMT

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;

Re: Month-Year Loop

RW9 — Wed, 10 Aug 2016 12:32:36 GMT

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?

Re: Month-Year Loop

Kurt_Bremser — Wed, 10 Aug 2016 12:37:31 GMT

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

Re: Month-Year Loop

sasmaverick — Wed, 10 Aug 2016 12:40:38 GMT

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

Re: Month-Year Loop

Kurt_Bremser — Wed, 10 Aug 2016 12:44:45 GMT

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

Re: Month-Year Loop

Astounding — Wed, 10 Aug 2016 12:49:12 GMT

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.

Re: Month-Year Loop

sasmaverick — Wed, 10 Aug 2016 15:33:13 GMT

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

Month

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

Re: Month-Year Loop

Astounding — Wed, 10 Aug 2016 15:49:24 GMT

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.

Re: Month-Year Loop

ballardw — Wed, 10 Aug 2016 15:49:31 GMT

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.

Re: Month-Year Loop

sasmaverick — Wed, 10 Aug 2016 19:47:11 GMT

Yours alongwith Ballardw's suggestion helped me.

Thanks