How to compress letter in date column when import Excel file to SAS

[hhchenfx]

Hello Everyone,

I have an Excel file attached where in column date (mm/dd/yyyy) there is cell like ?02/20/2019 or 1/1/2019!

Thus when I do proc import, the whole date column turn to text.

Is there anyway to compress these character and  import the file correctly?

Thank you,

HHCFX

[Tom]

Yes. Open the file in Excel and fix the column so that only contains dates.  Or convert the column so that it only contains strings that look like dates that you can then convert after importing.  Or save the file to a text file (like a CSV file) and write your own data step to read it.

Or better still don't use Excel for data entry at all.  It is not really suited to use for a database or date entry tool.  It was designed for doing spreadsheet type calculations where each cell is independent.   

If you really have to deal with a column that has mixed dates and text then you will find that the actual dates will appears a digit strings that represent the way that Excel counts days.

data want;
  set have ;
  if 3 < length(chardate) <= 5 and not missing(input(chardate,??5.) then
    numdate = input(chardate,5.) + '30DEC1899'd ;
  else numdate=input(compress(chardate,'?!'),mmddyy10.);
  format numdate yymmdd10.;
run;

[hhchenfx]

Thank you, Tom.

Your code works with other cells but not the first one where : *!9/20/2017

Yes, I got to deal with that messy data.

HHCFX

[Tom · Accepted solution]

Read up on the features of the COMPRESS() function.

data have ;
  input chardate $20. ;
cards;
*!9/20/2017
;

data want;
  set have;
  numdate=input(compress(chardate,'-/.','kd'),mmddyy10.);
  format numdate yymmdd10.;
run;

proc print;
run;

Obs     chardate         numdate

 1     *!9/20/2017    2017-09-20

[hhchenfx]

Thanks a lot, Tom.

HHC