How to change multiple dates format into uniform format in SAS

[stevenyan0127]

Hi everyone, 

I would like to:

• convert all date formats into mmddyy10.

• All of these variables are in numeric form

• For those cells without year value, I would like to add year 2020

Here's a preview of the data: 

A	B	C	D	E	F	G	H	I	J	K	L
3/20/20	4/2/20	3/21/2020	4/1/2020	8-Apr	12-Aug	10-Jun	10-Jun	12-Aug	4-Sep	8/12/2020	12-Aug
4/7/20	5/1/20	4/10/2020	4/24/2020	9-Jun	8-Jun	19-Oct	21-Oct			1/15/2021	15-Jan
4/6/20	5/11/20	4/6/2020	5/3/2020	25-Jun			1-Oct
3/16/20	4/16/20	3/17/2020	4/8/2020	6-May	11-Aug		17-Jun	11-Aug
3/27/20	4/12/20	3/30/2020	4/10/2020	1-May	19-Jun	19-May	19-May	18-May
3/20/20	4/13/20	3/25/2020	4/10/2020	2-May	13-Nov	2-Jun	2-Jun	4-Aug	3-Sep	8/4/2020	4-Aug
3/27/20	4/16/20	3/27/2020	4/10/2020	1-May	28-Jul	25-Jul	29-May	27-Jul		7/28/2020
3/26/20	4/11/20	3/30/2020	4/9/2020	7-May	2-Jun	21-Jul	21-Jul	21-Jul	10-Sep
4/14/20	5/10/20	4/16/2020	5/6/2020	12-Jun		26-Aug
3/18/20	4/18/20	3/22/2020	4/5/2020	16-May	20-Oct	28-Jul	10/27/2020	20-Oct	22-Sep	10/27/2020	10/27/2020
4/18/20	7/5/20	4/19/2020	6/15/2020	10-Aug		8/21/2021	23-Dec
4/24/20	6/28/20	4/29/2020	6/15/2020	7/9/2020	4-Sep	3-Aug		18-Jul		3/11/2021
3/25/20	4/12/20	3/29/2020	4/8/2020	5-May	4-Aug	4-Aug	4-Aug	4-Aug		11/5/2020

I have attached the data below. I would appreciate all the help. Thanks in advance!

[Tom]

Here is how this FORUM displays the file you attached.

That sheet is going to cause trouble for importing into SAS as it appears to have at least one extra row above the actual place where the tabular data layout starts.  But assuming you use the right code to import just the data table then only C and D look like they contain text.  The other fields look like they contain dates and should be imported properly as SAS date values.  The ones that do not appear to have a year are right aligned so they are probably numeric values so the year is there, it is just not being displayed on the screen. Once you have SAS date values you can attach any format that knows how to display date values, so let's use DATE9. in our example code.

format A B E--L date9.;

To convert C and D into dates you will need to use the INPUT() function to make new numeric variables.  They appear to be using M-D-Y order in those strings so use the MMDDYY informat.

c_date = input(c,mmddyy10.);
d_date = input(d,mmddyy10.);
format c_date d_date date9.;

[stevenyan0127]

Hi, thanks Tom! I have re-uploaded the sheet and should appear normal now. I will try your codes.

[Tom]

So that version does not have any issues.  PROC IMPORT makes everything a date.

The CONTENTS Procedure

    Alphabetic List of Variables and Attributes

 #    Variable    Type    Len    Format      Label

 1    A           Num       8    MMDDYY8.    A
 2    B           Num       8    MMDDYY8.    B
 3    C           Num       8    MMDDYY8.    C
 4    D           Num       8    MMDDYY8.    D
 5    E           Num       8    DATE5.      E
 6    F           Num       8    DATE5.      F
 7    G           Num       8    DATE5.      G
 8    H           Num       8    DATE5.      H
 9    I           Num       8    DATE5.      I
10    J           Num       8    DATE5.      J
11    K           Num       8    MMDDYY8.    K
12    L           Num       8    DATE5.      L

Use whatever date type format you want with any of those variables.  I would avoid either MMDDYY or DDMMYY as either choice will confuse half of your audience.  Use DATE9. or YYMMDD10. .

[data_null__]

Looks like they are all EXCEL dates.

A	B	C	D	E	F	G	H	I	J	K	L
43910	43923	43911	43922	43929	44055	43992	43992	44055	44078	44055	44055
43928	43952	43931	43945	43991	43990	44123	44125			44211	44211
43927	43962	43927	43954	44007			44105				
43906	43937	43907	43929	43957	44054		43999	44054			
43917	43933	43920	43931	43952	44001	43970	43970	43969			
43910	43934	43915	43931	43953	44148	43984	43984	44047	44077	44047	44047
43917	43937	43917	43931	43952	44040	44037	43980	44039		44040	
43916	43932	43920	43930	43958	43984	44033	44033	44033	44084		
43935	43961	43937	43957	43994		44434					
43908	43939	43912	43926	43967	44124	44040	44131	44124	44096	44131	44131
43939	44017	43940	43997	44053		44429	44553				
43945	44010	43950	43997	44021	44078	44046		44030		44266	
43915	43933	43919	43929	43956	44047	44047	44047	44047		44140	

A	B	C	D	E	F	G	H	I	J	K	L
43910	43923	43911	43922	43929	44055	43992	43992	44055	44078	44055	44055
43928	43952	43931	43945	43991	43990	44123	44125			44211	44211
43927	43962	43927	43954	44007			44105
43906	43937	43907	43929	43957	44054		43999	44054
43917	43933	43920	43931	43952	44001	43970	43970	43969
43910	43934	43915	43931	43953	44148	43984	43984	44047	44077	44047	44047
43917	43937	43917	43931	43952	44040	44037	43980	44039		44040
43916	43932	43920	43930	43958	43984	44033	44033	44033	44084
43935	43961	43937	43957	43994		44434
43908	43939	43912	43926	43967	44124	44040	44131	44124	44096	44131	44131
43939	44017	43940	43997	44053		44429	44553
43945	44010	43950	43997	44021	44078	44046		44030		44266
43915	43933	43919	43929	43956	44047	44047	44047	44047		44140

[Tom · Accepted solution]

If you already have a SAS dataset you can use the FMTINFO() function to find which of the variables are dates so help automate changing the format attached to them.

proc import datafile='c:\downloads\dates.xlsx' dbms=xlsx out=dates replace;
run;

proc contents noprint data=dates out=contents;
run;

proc sql noprint;
  select nliteral(name) into :dates separated by ' ' 
  from contents
  where 'date'=fmtinfo(format,'cat')
  ;
%let ndates=&sqlobs;
quit;

%if (&ndates) %then %do;
proc datasets nolist lib=work;
  modify dates;
    format &dates date9.;
  run;
quit;
%end;

proc print data=dates(obs=5);
run;

Results

Obs         A         B         C         D         E         F         G         H         I         J         K         L

  1 20MAR2020 02APR2020 21MAR2020 01APR2020 08APR2022 12AUG2022 10JUN2022 10JUN2022 12AUG2022 04SEP2022 12AUG2020 12AUG2022
  2 07APR2020 01MAY2020 10APR2020 24APR2020 09JUN2022 08JUN2022 19OCT2022 21OCT2022         .         . 15JAN2021 15JAN2022
  3 06APR2020 11MAY2020 06APR2020 03MAY2020 25JUN2022         .         . 01OCT2022         .         .         .         .
  4 16MAR2020 16APR2020 17MAR2020 08APR2020 06MAY2022 11AUG2022         . 17JUN2022 11AUG2022         .         .         .
  5 27MAR2020 12APR2020 30MAR2020 10APR2020 01MAY2022 19JUN2022 19MAY2022 19MAY2022 18MAY2022         .         .         .