Convert Date strings to actual dates

Accepted Solution Solved
Reply
Super Contributor
Posts: 418
Accepted Solution

Convert Date strings to actual dates

If I remember correctly there is a way inherently in sas to convert date strings (in word format) into actual number dates. Can anyone link the code that would accomplish this for the following date examples (none have days so I would like the day to default to 1).

January 2011       BECOMES      1-1-2011

March 2009          BECOMES      3-1-2009.

ETC...

This also needs to happen inside of a proc sql step, so the code has to work in a proc sql step (which shouldn't eliminate anything, just wanted to bring it up just in-case). If there is not a specific informat for this, please let me know! (although I could swear I did something like this years ago with an informat).

Ex:

proc sql;

create table answer as

select input(date,DATEFORMATX.) as Dateanswer

from mytable;

quit;

run;

All of the months values are fully spelled out with the 4 digit year attached.

Thanks!

Brandon


Accepted Solutions
Solution
‎07-24-2013 04:00 AM
SAS Super FREQ
Posts: 708

Re: Convert Date strings to actual dates

Posted in reply to Anotherdream

The ANYDTDTE. informat almost does what you need but you need to tweak the data a bit (add a day), see example below:

data have;
  infile cards truncover;
 
input @1 dateString $32. @;
  input @1 mydate anydtdte32. @;
  newDate = tranwrd( strip(dateString), " ", " 1, ");
  newDate2 = input(newDate, anydtdte32.);
  format mydate newDate2 date9.;
 
cards;
January 2011
February 2011
March 2011
April 2011
May 2011
June 2011
July 2011
August 2011
September 2011
October 2011
November 2011
December 2011
;

View solution in original post


All Replies
Super User
Super User
Posts: 7,074

Re: Convert Date strings to actual dates

Posted in reply to Anotherdream

I don't know of any that work.  You can use functions to pull it apart and put it back together in a format that does exist.

date = input(cats(substr(char,1,3),scan(char,2)),monyy7.);

Super Contributor
Posts: 418

Re: Convert Date strings to actual dates

Hello Tom, yeah this is actually what I did in my current implimentation of the process, I was just curious because I could have sworn I'd seen one before.

Oh well, I can't seem to find it online, and if you also don't think one exists I probably just dreamt of it!

I gotta stop dreaming of SAS, there's probably a pill I can take for that......

Super User
Posts: 11,343

Re: Convert Date strings to actual dates

Posted in reply to Anotherdream

You were probably thinking of the WORDDATE display format. Unfortunately there isn't a corresponding INFORMAT. There is MMMYY that reads things like JUN2013 without spaces and only the 3 letter abbreviation though.

Super Contributor
Posts: 297

Re: Convert Date strings to actual dates

Posted in reply to Anotherdream

Hi Another,

I have used the following method to deal with 'odd' dates in the past and it might be useful to you.  You will need to play around with to DO LOOP to capture the date range you are after, but the rest should remain unchanged.  If you are handling this type of date infrequently then it may not be worth your time.  We had a few systems where the format was the same and was causing us problems so this solution worked well for us.

DATA HAVE;

LENGTH DATES1 $14;

INPUT DATES1 $;

INFILE DATALINES DLM="," MISSOVER;

DATALINES;

January 2011

February 2011

March 2011

April 2011

May 2011

June 2011

July 2011

August 2011

September 2011

October 2011

November 2011

December 2011

;

PROC FORMAT ;

PICTURE MONTHSYYYY LOW-HIGH = '%B %Y'

(DATATYPE = DATE) ;

RUN ;

DATA INFMT ;

RETAIN FMTNAME "MONTHSYYYY" TYPE "I" ;

DO I = 1 TO 200;

LABEL = INTNX("MONTH",TODAY(),-I,"BEGINNING");

START = TRIM(LEFT(PUT(LABEL,MONTHSYYYY14.))) ;

OUTPUT ;

END ;

RUN ;

PROC FORMAT CNTLIN = INFMT ;

RUN;

PROC SQL;

CREATE TABLE WANT AS

SELECT INPUT(DATES1,MONTHSYYYY14.) AS DATEANSWER FORMAT = MMDDYYD10.

FROM HAVE;

QUIT;

Solution
‎07-24-2013 04:00 AM
SAS Super FREQ
Posts: 708

Re: Convert Date strings to actual dates

Posted in reply to Anotherdream

The ANYDTDTE. informat almost does what you need but you need to tweak the data a bit (add a day), see example below:

data have;
  infile cards truncover;
 
input @1 dateString $32. @;
  input @1 mydate anydtdte32. @;
  newDate = tranwrd( strip(dateString), " ", " 1, ");
  newDate2 = input(newDate, anydtdte32.);
  format mydate newDate2 date9.;
 
cards;
January 2011
February 2011
March 2011
April 2011
May 2011
June 2011
July 2011
August 2011
September 2011
October 2011
November 2011
December 2011
;
Super Contributor
Posts: 418

Re: Convert Date strings to actual dates

Posted in reply to Bruno_SAS

The anydtdte function (not anydate) function was the one I was looking for, thank you.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 561 views
  • 0 likes
  • 5 in conversation