Month to Calendar Month

[Timbim]

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

[RW9]

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 · Accepted solution]

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]

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]

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]

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]

Got it.

Thanks for explaining it so clearly.

Kind regards,

Magstart