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.
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
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
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
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.