topic Re: Convert Date strings to actual dates in SAS Programming

Convert Date strings to actual dates

Anotherdream — Mon, 22 Jul 2013 21:36:02 GMT

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

Re: Convert Date strings to actual dates

Tom — Mon, 22 Jul 2013 22:00:21 GMT

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.);

Re: Convert Date strings to actual dates

Anotherdream — Mon, 22 Jul 2013 22:08:25 GMT

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......

Re: Convert Date strings to actual dates

ballardw — Mon, 22 Jul 2013 22:44:10 GMT

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.

Re: Convert Date strings to actual dates

Scott_Mitchell — Tue, 23 Jul 2013 02:18:17 GMT

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;

Re: Convert Date strings to actual dates

BrunoMueller — Wed, 24 Jul 2013 08:00:35 GMT

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
;

Re: Convert Date strings to actual dates

Anotherdream — Thu, 08 Aug 2013 20:43:05 GMT

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