topic Excel date-time to SAS Viya in SAS Visual Analytics

Excel date-time to SAS Viya

Kebru — Sun, 25 Oct 2020 10:24:12 GMT

Hey,

i'm pretty new in the SAS world. I need to create a dashboard for my exam in the university.

I want to upload a excel sheet to SAS Viya. The upload is done via a Unicode text (*.txt). The upload itself works, but I can't manage to transfer the date into a clever format. The date in Excel is user defined in a german format (DD.MM.YYYY hh:mm:ss). How do I get the date converted to a SAS compatible fortmat? Unfortunately I am not familiar with SAS programming. Can I make settings in Excel?

Thanks a lot. Greeting from Germany.

Re: Excel date-time to SAS Viya

jimbarbour — Sun, 25 Oct 2020 15:24:09 GMT

@Kebru,

SAS should be able to read almost any datetime value whether from Germany, Ghana or Guiana.

For example, if I have a datetime formatted as 25.10.2020 09:49:25, the following SAS code will read that datetime value quite well. Your LOCALE setting typically determines whether the date portion is read Day-Month-Year (common in Europe) or Month-Day-Year (common in North America).

DATA	German_Formatted_Dates;
	INPUT	SAS_Date_Time	&	:	ANYDTDTM19.;
	Fmt_SAS_Date_Time	=	SAS_Date_Time;
	FORMAT	Fmt_SAS_Date_Time		DATETIME19.;
DATALINES;
25.10.2020 09:49:25
;
RUN;

Results:

Notice that I have two values: An unformatted value and a formatted value. Both values are equal. The second value has a FORMAT applied to it, and therefore is displayed as 25OCT2020 09:49:25 but is stored as 1919238565.

The difference between how data is formatted (or displayed) vs. how data is stored is important. I suspect the German formatted datetime values in your Excel file is actually stored as an an Excel datetime value.

For example, the formatted datetime value 25.10.2020 09:49:25 would be stored as 44129.4093171296 in Excel. There is a conversion formula to change the value to the equivalent value in SAS: SAS_date_time = (Excel_date_time - 21916) * 86400;

The following SAS code takes an Excel datetime for 25OCT2020 09:49:25 and converts it into a SAS datetime value.

DATA	Excel_Dates;
	INPUT	Excel_Date_Time;
	SAS_date_time = (Excel_date_time - 21916) * 86400;
	FORMAT	SAS_Date_Time		DATETIME19.;
DATALINES;
44129.4093171296
;
RUN;

Results:

I think you will need this conversion formula to bring your data into SAS.

Jim

Here is a paper on the subject if it is helpful: https://support.sas.com/resources/papers/proceedings/proceedings/sugi29/068-29.pdf

Re: Excel date-time to SAS Viya

Sam_SAS — Tue, 27 Oct 2020 13:03:42 GMT

Jim's answer should work if you have permissions to perform data preparation in your environment.

If you can only import and format the data within Visual Analytics, you can format the date values by creating a calculated item that uses the Parse() operator.

Let us know if this helps,

Sam