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
The ANYDTDTE. informat almost does what you need but you need to tweak the data a bit (add a day), see example below:
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.);
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......
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.
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;
The ANYDTDTE. informat almost does what you need but you need to tweak the data a bit (add a day), see example below:
The anydtdte function (not anydate) function was the one I was looking for, thank you.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.