topic Re: Month to Calendar Month in SAS Programming

Month to Calendar Month

Timbim — Thu, 13 Sep 2018 08:12:47 GMT

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

Re: Month to Calendar Month

RW9 — Thu, 13 Sep 2018 08:24:22 GMT

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.

Re: Month to Calendar Month

gamotte — Thu, 13 Sep 2018 08:34:13 GMT

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;

Re: Month to Calendar Month

RW9 — Thu, 13 Sep 2018 08:45:02 GMT

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.

Re: Month to Calendar Month

Timbim — Fri, 21 Sep 2018 05:26:53 GMT

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.

Re: Month to Calendar Month

gamotte — Fri, 21 Sep 2018 08:02:43 GMT

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)

Re: Month to Calendar Month

Timbim — Fri, 19 Oct 2018 00:07:31 GMT

Got it.

Thanks for explaining it so clearly.

Kind regards,

Magstart