BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Anotherdream
Quartz | Level 8

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

1 ACCEPTED SOLUTION

Accepted Solutions
BrunoMueller
SAS Super FREQ

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

6 REPLIES 6
Tom
Super User Tom
Super User

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

Anotherdream
Quartz | Level 8

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

ballardw
Super User

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.

Scott_Mitchell
Quartz | Level 8

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;

BrunoMueller
SAS Super FREQ

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
;
Anotherdream
Quartz | Level 8

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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