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

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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

12 REPLIES 12
Reeza
Super User

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.;
sasmaverick
Obsidian | Level 7

Hi Reeza,

 

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

 

Regards,

 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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?

Kurt_Bremser
Super User

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

KachiM
Rhodochrosite | Level 12

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;

sasmaverick
Obsidian | Level 7

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

Astounding
PROC Star

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.

sasmaverick
Obsidian | Level 7

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

 

Astounding
PROC Star

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.

sasmaverick
Obsidian | Level 7

Yours alongwith Ballardw's suggestion helped me.

 

Thanks

ballardw
Super User

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.

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 12 replies
  • 5698 views
  • 4 likes
  • 7 in conversation