topic Re: Importing Excel sheet with costum formatted variables in SAS Programming

Importing Excel sheet with costum formatted variables

Ihsan-Mahdi — Wed, 06 Mar 2024 16:50:27 GMT

Hello,

I'm trying to read an Excel sheet in SAS using proc import. The sheet has date/time variables formatted as m/d/yyyy h:mm.

When imported to SAS, the variables are converted to string.

For example a value in the Excel sheet of: 12/31/2020 10:42:00 PM is imported as a character variable with the value: 44196.94583333333.

When I try converting the character variable to numeric, SAS doesn't allow me to apply the DATETIME. format, it only works if I use the format 20. When I apply the DATETIME. format to the newly created numeric variable (formatted 20.), I get the wrong date!

Is there a way to fix this? Thank you:)

Re: Importing Excel sheet with costum formatted variables

Tom — Wed, 06 Mar 2024 17:05:08 GMT

The column has at least one cell that does NOT have a numeric value in it (Excel stores dates and datetime values are numbers). So SAS had to make a CHARACTER variable so it could store that cell with the character string in it.

The main fix is to make sure each column in the worksheet uses only one type of values. Either all numbers or all characters.

If you cannot do that then make a new variable in SAS that has a datetime value by converting the strings SAS created to store the raw number of days (and fraction of days) that EXCEL uses to store date (and datetime) values into a number and adjust for the difference in where they start counting (and Excel mistaken inclusion of Feb 29 in the year 1900).

So for the actual datetime values, like 44196.94583333333, this will work.

data want;
  set have;
  newvar = dhms(input(oldvar,32.)+'30DEC1899'd,0,0,0);
  format newvar datetime19.;
run;

But what every gibberish character string was there that made SAS think the column has mixed cell types will probably cause the INPUT() function to fail. So you might need to add some test to see what those values are and decide if they can also be converted into valid datetime values.

Re: Importing Excel sheet with costum formatted variables

Ihsan-Mahdi — Wed, 06 Mar 2024 17:20:28 GMT

Thank you so much for a great response. The code you provided worked in creating a new variable with correct values! I'm still unsure why the conversion to character happened! I made sure no mixed values were in the file before importing! I guess I must've missed something.

My data also has a Birth Date variable which was treated the same way. What SAS format would work on it?

Re: Importing Excel sheet with costum formatted variables

Tom — Wed, 06 Mar 2024 22:06:37 GMT

@Ihsan-Mahdi wrote:

Thank you so much for a great response. The code you provided worked in creating a new variable with correct values! I'm still unsure why the conversion to character happened! I made sure no mixed values were in the file before importing! I guess I must've missed something.

My data also has a Birth Date variable which was treated the same way. What SAS format would work on it?

If you have a DATE column (no time of day component) then skip the DHMS() function and use the DATE format (or any other format you like that works with DATE values).

newvar=input(oldvar,32.)+'30DEC1899'd;
format newvar date9.;