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

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

hhchenfx — Mon, 25 Mar 2019 02:57:49 GMT

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

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

Tom — Mon, 25 Mar 2019 04:55:09 GMT

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;

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

hhchenfx — Mon, 25 Mar 2019 21:32:52 GMT

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

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

Tom — Mon, 25 Mar 2019 22:15:17 GMT

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

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

hhchenfx — Fri, 29 Mar 2019 01:56:41 GMT

Thanks a lot, Tom.

HHC