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?
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).
INPUT SAS_Date_Time & : ANYDTDTM19.;
Fmt_SAS_Date_Time = SAS_Date_Time;
FORMAT Fmt_SAS_Date_Time DATETIME19.;
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.
SAS_date_time = (Excel_date_time - 21916) * 86400;
FORMAT SAS_Date_Time DATETIME19.;
I think you will need this conversion formula to bring your data into SAS.
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.