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
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
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;
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.
Find more tutorials on the SAS Users YouTube channel.