topic Re: converting months to numbers in SAS Programming

converting months to numbers

ubshams — Tue, 06 Oct 2020 14:41:20 GMT

I have a dataset which has a column named Month in yymmdd10. format.

How can I use the information in the Month column to create a new column called

Month2 whereby the data is in the following format - 200901, 200902, ...200912:

for e.g.

200901 for 2009-01-01

201212 for 2012-01-01

..etc

I would like Month2 to be just numbers.

Thanks in advance!

Re: converting months to numbers

PaigeMiller — Tue, 06 Oct 2020 14:49:48 GMT

Don't create a new column. Just assign the YYMMN6. format to this variable.

Re: converting months to numbers

ubshams — Tue, 06 Oct 2020 14:55:23 GMT

Thanks but I actually need the new column to be in a numbers format to match with

our internal database. So they need to be in the 200901, 200902, etc format.

Re: converting months to numbers

mklangley — Tue, 06 Oct 2020 15:09:59 GMT

@ubshams Like this?

data have;
    input month :yymmdd10.;
    format month yymmdd10.;
    datalines;
    2009-01-01
    2012-01-01
    2020-10-06
    ;
run;

data want;
    set have;
    month2 = input(put(month, yymmn6.), 6.);
run;

Re: converting months to numbers

novinosrin — Tue, 06 Oct 2020 15:10:13 GMT

New_col=input(put(date,yymmn6. -l),6.);

Re: converting months to numbers

Reeza — Tue, 06 Oct 2020 15:41:36 GMT

month_number = year(date)*100 + month(date);

@ubshams wrote:

I have a dataset which has a column named Month in yymmdd10. format.

How can I use the information in the Month column to create a new column called

Month2 whereby the data is in the following format - 200901, 200902, ...200912:

for e.g.

200901 for 2009-01-01

201212 for 2012-01-01

..etc

I would like Month2 to be just numbers.

Thanks in advance!

Re: converting months to numbers

PaigeMiller — Tue, 06 Oct 2020 15:55:32 GMT

@ubshams wrote:

Thanks but I actually need the new column to be in a numbers format to match with

our internal database. So they need to be in the 200901, 200902, etc format.

Actually, no you don't. When you need numbers to be 200901, 200902, etc, you use the YYMMN6. format. When you want them to be 2009-01-01 you use the YYMMDD10. format. And so on. This leaves the value as a true SAS date value.

The solution you marked correct does not result in SAS date values, it results in integers where you have to provide the logic to find what month comes before 201001 and how many months are in between 200908 and 201006, and so on. Many drawbacks.