Convert Character Date variable to SAS Date

Question

I have the following Variable called Date in an excel file which I'm reading into SAS:

Date
May2005
June2005
July2005
..
July2015

Both the format and the informat are characters ($8)

I wanted to convert these into a SAS Date variable. How can I accomplish this task?

I thought about using substr to first create a month and year variable, then use proc format to convert all the months to numeric (e.g 'jan' = 1). The use the mdy date function to create a new date. But I wonder if there is a shorter way to accomplish this task?

Answer 1

You can use the ANYDTDTE. informat if you prepend a day to your month+year string.

data want ;
  set have ;
  actual_date = input('01'||date,anydtdte.);
  format actual_date date9.;
run;

Note that the FORMAT or INFORMAT attached to the character variable is meaningless, but having a variable of only length 8 will not allow room to store longer month names. Perhaps the length got set to only 8 because your particular example set of data did not include any longer month names.

If you are running such an old version of SAS that the ANYDTDTE. informat does not exist or does not work with fully spelled out months then you will need to work a little harder. You could transform the string into DATE9 format.

  actual_date = input
    ('01'||substr(date,1,3)||substr(date,length(date)-3)
    ,DATE9.);

Answer 2

As @Tom hints towards, you have to use an informat that SAS can interpret as a numeric value when reading in character dates. I'm not sure if there is one that reads MONTHYYYYw., (naturally, ANYDTDTE works but I prefer to avoid it). In this case, I would use MONYYw., combined with substr to get the length 3 Month abbreviation and the 2 digit year:

data have;
  input Date $13.;
  datalines;
  January2005
  Feburary2005
  March2005
  April2005
  May2005
  June2005
  July2005
  August2005
  September2005
  October2005
  November2005
  December2005
  ;
run;

data want;
  set have;
  Date2 = input(SUBSTR(Date,1,3)||SUBSTR(Date,length(date)-1,2),MONYY13.);
  Format Date2 DATE8.;
run;

proc print data = want; run;