How Do I convert a string like 09NOV2020 to the numeric format 09-09-20?

[Maria8]

09NOV2020 is usually DATE9, but I'm not sure where to put this here. Ideally, I'm trying to convert from DATE9. to MMDDYYD10. 

data test 

     dt1: "09NOV2020" ;

     dt2: input(dt1, MMDDYYD10.); 

     format dt2 MMDDYYD10.; 

run; 

[PaigeMiller]

So you can't convert a string 09NOV2020 to 09-09-2020, but you can convert it to either 09-11-2020 or 11-09-2020 (whichever you prefer)

data test;
     dt1= "09NOV2020" ;
     dt2= input(dt1, date9.); 
     format dt2 mmddyyd10.; 
run; 

That gives you 11-09-2020. If you want 09-11-2020, its an obvious modification to the above code.

[Maria8]

Thanks! If I did that, it would convert for all of the dates in the sheet, right? Would I need to create another table for changed results, or could I just use that and convert everything?

And test would be the name of the file, right?

[Tom]

TEST was the name of the dataset that the example test program program was creating to show you how to transform a string into a date.  It did not read in any existing data instead it just hardcoded the example string value.

In general it is better to create a new dataset with the changes. If you try to modify your existing dataset and make a mistake you might lose data.

So if your existing "sheet" has been converted into a SAS dataset named HAVE then you can use a data step like this to create a new dataset named WANT.

data want;
  set have;
  ....
run;

Replace the ... with the SAS data step statements todo the calculations you want.  Like these statements :

  dt2 = input(dt1, date9.); 
  format dt2 MMDDYYD10.; 

[Maria8]

Is there a way to automatically convert all of the values that are in date9. format to mmddyyd10. without having to input each and every value?

[Maria8]

Gotcha! I think the mistake that I made in the original code is that I used 09NOV2020 as an example - I'd like to change all of the dates in the 500K dataset from date9. to mmddyyd10.

How would I do that?

[Tom · Accepted solution]

Your example code is a little off.  Assignment statements use = , not : .

There is no informat named DDMMYYD.  There is one named DDMMYY that will read strings in the style produced by the MMDDYYD format.  But your strings are not in that style.  They are in the style that the DATE informat can read.

data test ;
  dt1 = "09NOV2020" ;
  dt2 = input(dt1, date9.); 
  format dt2 MMDDYYD10.; 
  put dt1= dt2=;
run; 

802   data test ;
803     dt1 = "09NOV2020" ;
804     dt2 = input(dt1, date9.);
805     format dt2 MMDDYYD10.;
806     put dt1= dt2=;
807   run;

dt1=09NOV2020 dt2=11-09-2020
NOTE: The data set WORK.TEST has 1 observations and 2 variables.

PS:  Why would you want to display dates in MDY order?  That will confuse half of your audience. If you don't want to use DATE format with your new date variable then use YYMMDD format and then no one will confuse December tenth for the twelfth of October.