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
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.
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.;
Hi Reeza,
I tried this, but I am getting 2304 observation when in fact I should only get 48.
Regards,
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?
@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.
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;
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
@Reeza's code does not need that simple dataset 1 to 48 at all. Instead it creates the list of months on its own.
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.
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
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.
Yours alongwith Ballardw's suggestion helped me.
Thanks
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.