convert text 'MMM-DD-YYYY' to date 'MM/DD/YYYY' and 'DDMMMYYYY'

[Alexxxxxxx]

hi,

How to convert text 'MMM-DD-YYYY' (e.g., Dec-24-2020)

to date 'MM/DD/YYYY' (e.g., '12/24/2020')

and date 'DDMMMYYYY' (e.g., '24DEC2020')

data table1;
	infile cards truncover;
	input
	start_date $200.
	;
	cards;
	Dec-24-2020
	Feb-11-2020
	Apr-13-2018
	;;;;
	run;

Could you please give me some suggestions? Thanks in advance.

[jimbarbour]

Something like this perhaps? 

data table1;
	infile cards truncover;
	input
	start_date $200.
	;
	Temp_Date = INPUT(STRIP(start_date), ANYDTDTE11.);
	Date_mmddyyyy = PUT(Temp_Date, MMDDYYS10.);
	Date_ddmmmyyyy = PUT(Temp_Date, DATE9.);
	PUTLOG "NOTE:  "    Date_mmddyyyy= Date_ddmmmyyyy=;
cards;
Dec-24-2020
Feb-11-2020
Apr-13-2018
;;;;
run;

I haven't double checked my formats yet, but this is along the lines of what you would need to do.

I have now checked my formats and informats. 

The basic idea is to read in the character date into a numeric SAS date field using the INPUT function and a date informat.  Then, by using the PUT function, I can convert the numeric SAS date field into any character format that I want.  

INPUT takes character and transforms it to numeric (dates are numeric) using an Informat.

PUT takes numeric and transforms it to character using a Format.

Jim

[Kurt_Bremser]

Hi @jimbarbour !

I couldn't find a MMMDDYY informat in the documentation. Is it an undocumented one?

[jimbarbour]

No, that's an error.  I corrected it.  

Jim

[Alexxxxxxx]

Hi @jimbarbour ,

Many thanks for your reply. Is that possible to store the date as a numeric value but shown as the date format? 

[CurtisMackWSIPP]

Just and a format statement like this:

data result;
  set table1;
  format realdate Date10.;
  dateDDMMMYYYY = upcase(catt(scan(start_date,2),scan(start_date,1),scan(start_date,3)));
  realdate = input(dateDDMMMYYYY,Date10.);
  MMDDYYSdate = put(realdate,MMDDYYS10.);
run;

[jimbarbour · Accepted solution]

@Alexxxxxxx,

---

@Alexxxxxxx wrote:

Hi @jimbarbour ,

Many thanks for your reply. Is that possible to store the date as a numeric value but shown as the date format? 

---

Yes, but you would have to choose one format or the other -- or have two separate variables.  Let's say we want MM/DD/YYYY to be the default representation.  You would change the code like so:

data table1;
	DROP	In_Date;
	FORMAT	Start_Date	MMDDYYS10.;

	infile cards truncover;
	input
	In_Date $200.
	;

	Start_Date = INPUT(STRIP(In_Date), ANYDTDTE11.);
	PUTLOG "NOTE:  "    Start_Date=;
	PUTLOG "NOTE-  "    Start_Date= DATE9.;
	PUTLOG "NOTE-  ";
cards;
Dec-24-2020
Feb-11-2020
Apr-13-2018
;;;;
run;

Now, if you look at the results, you will see that they appear in MM/DD/YYYY format, but look at the icon.  It is a date icon not a character icon.  The value is stored internally as a numeric date but is represented in the format we want.

Notice my log statements:

	PUTLOG "NOTE:  "    Start_Date=;
	PUTLOG "NOTE-  "    Start_Date= DATE9.;
	PUTLOG "NOTE-  ";

The first has no format.  Since we applied a default or "permanent" format to Start_Date, the MM/DD/YYYY format will be used.

However, if we want to display the Start_Date in another format, then we simply specify that format after the variable name.

Our log looks like this:

NOTE:  Start_Date=12/24/2020
       Start_Date=24DEC2020
       
NOTE:  Start_Date=02/11/2020
       Start_Date=11FEB2020
       
NOTE:  Start_Date=04/13/2018
       Start_Date=13APR2018

Jim

[CurtisMackWSIPP]

This works:

data result;
  set table1;
  dateDDMMMYYYY = upcase(catt(scan(start_date,2),scan(start_date,1),scan(start_date,3)));
  realdate = input(dateDDMMMYYYY,Date10.);
  MMDDYYSdate = put(realdate,MMDDYYS10.);
run;

[jimbarbour]

Oh, @Alexxxxxxx,

I should mention one more thing:  The ANYDTDTE11. format is a wonderful format, but you have to be a little bit careful.

For example, consider this date:

08/09/2020

Is that August 9th or is that September 8th?  In the US, that would be August 9th.  In Europe, typically, that would be September 8th.  Your DATESTYLE and LOCALE settings determine whether a date will be interpreted as MM/DD/YYYY or DD/MM/YYYY.

@Rick_SAS has an excellent blog post on the subject:  https://blogs.sas.com/content/iml/2016/11/11/anydtdte-informat-read-any-date-sas.html

Jim