topic Re: Excel Values change in SAS in SAS Visual Analytics

Excel Values change in SAS

DandarahZ — Mon, 07 Oct 2019 06:47:48 GMT

Hi i have a project where i have to use data collected from sensors to create a dashboard. The data is saved in excel document and then later dispalyed in SAS VA.

The format of the data in question is in this format below:

yyyy:mm:dd hh:mm:ss

The data displayed in excel comes out legible as the format above. However, when pumped in to SAS VA the information is displayed as decimal numbers.

Can any one here clarify?

Regards

Dan

Re: Excel Values change in SAS

Kurt_Bremser — Mon, 07 Oct 2019 07:57:18 GMT

How do you read your Excel files into SAS?

Note that this is not a standardized format for datetimes. I'd much rather use the ISO 8601 format:

yyyy-mm-ddThh:mm:ss

Re: Excel Values change in SAS

Tom — Mon, 07 Oct 2019 15:01:16 GMT

Not sure how you are reading the data from Excel into VA, but if you use the PROC IMPORT available in SAS you will end up with strings that look like that if the column (variable) in the Excel sheet has mixed numeric (dates are stored as numbers in SAS and Excel) and character values. To pull that into a dataset SAS will need to make the variable a character string. But when it does that it just converts the numbers that Excel uses to store Date (and DateTime) values. Excel stores dates are the number of days since 1900 and time as a fraction of a day. So a datetime value looks like a number with fractional part.

If you can fix the report to not put character strings into any of the cells in the column with the datetime values then SAS should read it as a number and convert the value for you.

If you cannot then you will need to derive a new variable, since you cannot change the type of a variable from character to numeric.

SAS code like this should work. Not clear to me how to do that in VA.

data want;
  set have;
  date_var = dhms(input(char_var,32.)+'30DEC1899'd,0,0,0);
  format date_var datetime20.;
run;