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

I have had trouble converting month in numeric to calendar month.

The month column in my data is numeric, it shows the name of the month in the output table however the format is numeric.

My employer’s financial year runs from September to August so my data has 1 for September and 12 for August.

I am trying to convert month 1 ( September) to 30Sep17 etc.

How could I do that?

I truly value your help.

Magstar
1 ACCEPTED SOLUTION

Accepted Solutions
gamotte
Rhodochrosite | Level 12

Hello,

 

data have;
input month 2.;
cards;
1
2
3
4
10
11
12
;
run;

data want;
set have;
month2=put(intnx('month',mdy(mod(month+7,12)+1,1,2017),0,'e'),date9.);
run;

View solution in original post

6 REPLIES 6
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Please post test data in the form of a datastep!!  And show what you want the output to look like.  

 

I would suggest, as nothing really to work with, create format:

proc format;
  value mt
    1="SEP"
     2="OCT"
...
run;

Then use that format:

data want;
  set have;
  new_date=intnx('month',input(cats("01",put(month,mt.),"2017"),date9.),0,"e");
run;

Note, I am having to use the intnx() to move the value to the end of the month as for some reason you seem to want this - 30 is not an available day in Febuary for instance.  I would default to 01.

gamotte
Rhodochrosite | Level 12

Hello,

 

data have;
input month 2.;
cards;
1
2
3
4
10
11
12
;
run;

data want;
set have;
month2=put(intnx('month',mdy(mod(month+7,12)+1,1,2017),0,'e'),date9.);
run;
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Nice, didn't think of modding it to go over the year end, hence why I went with the format.  Need another cup of tea.

Timbim
Obsidian | Level 7

Hi Gamote,

 

Could you please explain what your code is doing?

 

data want;
set have;
month2=put(intnx('month',mdy(mod(month+7,12)+1,1,2017),0,'e'),date9.);
run;

 

I am only beginning to use SAS frequently now and have not used mdy or mod before

 

Thanks kindly,

Magstar.

gamotte
Rhodochrosite | Level 12

Sure.

 

mod(month+7,12)+1 is the modulo function (equivalent to % operator in some other languages) and is used to get the right month number from your month variable (1:jan, 2:feb,...)

 

mdy(...,1,2017) creates a date variable for the 1st of the selected month

 

intnx('month',...,0,'e') changes the date to the last day ('e' parameter) of the unchanged month ('month' and 0 parameters)

Timbim
Obsidian | Level 7

Got it.

 

Thanks for explaining it so clearly.

 

Kind regards,

Magstart

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 6 replies
  • 2438 views
  • 2 likes
  • 3 in conversation