how to convert character date to numeric and create SAS date value

[hatsumi]

Hi,

I have a column called "Month" and they are stored in character value as below.

I want to convert them into numeric format so that I can create a new variable called "date" to store SAS date value.

However, it is giving me an error "ERROR 48-59: The format $MONTHFMT was not found or could not be loaded."

Below is the code I wrote and I really appreciate if someone let me know how I can run the code without an error.

The first set of code to specify format run without an error.

The second set of code seem to have an issue.

The third set of code works if I replace "Month" with any numeric characters.

work.data_month

Year	Month
1999	January
1999	December
2000	February
2001	July

libname mylib 'C:\Users';

proc format lib=mylib;

value $monthfmt

'January' = 1

'February' = 2

'March' = 3

'April' = 4

'May' = 5

'June' = 6

'July' = 7

'August' = 8

'September' = 9

'October' = 10

'November' = 11

'December' = 12;

run;

libname mylib 'C:\Users';

data work.data_month;

set work.data_month;

format Month $monthfmt.;

run;

data work.data_month;

set work.data_month;

date = mdy(Month, 1, Year);

format date mmddyy10.;

run;

[jwillis · Accepted solution]

Hatsumi,

This is one solution.  I do not have a similar library so i commented out your libname statement.  I added extra months and years just to be certain the code works for me.

proc sql; drop table work.month; quit;
data work.month;
input year $4. month $10.;
datalines;
1999 January
1999 December
2000 February
2001 July
1999 March
2000 April
2002 June
2001 August
2000 September
2002 November
2001 October
2002 May
;
run;

     title " work.month ";
proc print data=work.month (obs=97);
run;
     title " ";

*libname mylib 'C:\Users';
proc format /*lib=mylib*/;
value $monthfmt(min=1 max=12)
'January'   = 1
'February'  = 2
'March'     = 3
'April'     = 4
'May'       = 5
'June'      = 6
'July'      = 7
'August'    = 8
'September' = 9
'October'   = 10
'November'  = 11
'December'  = 12;
run;

/*libname mylib 'C:\Users'; */

data work.data_month;
   set work.month;
format date mmddyy10.;
date = mdy(put(Month,$monthfmt.), 1, Year);
run;

     title " work.data_month ";
proc print data=work.data_month (obs=97);
run;
     title " ";

[hatsumi]

Hi Jwillis,

Thank you very much for your quick response!!!

I spent several hours wondering what is wrong with my code, so I really appreciate your help and it worked perfectly.

I cannot believe you figured out within minutes.. I wish I could write codes as efficiently as you one day.

Again, thanks so much!!

Best,

Hatsumi

[Ksharp]

OR Simpler.

data work.month;
input year $4. month $10.;
datalines;
1999 January
1999 December
2000 February
2001 July
1999 March
2000 April
2002 June
2001 August
2000 September
2002 November
2001 October
2002 May
;
run;
data month;
 set month;
 date=input(cats('01',substr(month,1,3),year),date9.);
 format date date11.;
run;

Xia Keshan

[hatsumi]

Hi Xia

This is great!!!

Thank you so much!!!

Although I knew all the functions you used, I never thought of using them to make the code simpler..

I guess it takes a lot more practice.

Thanks again!

Hatsumi