Is there a reliable way to import calendar dates from excel to SAS in the EPG?

[Gregor]

I am constantly having trouble importing calendar dates form excel to SAS.

Each time, I have to check if months are not switched to days etc.  In addition to that , Excel randomly converts around a lot.

My dream solution would be to directly import a German date format (saved as date in excel) 

06.11.2022 (November 6th, 2022)  to the right date (I don't care how its formatted in SAS).

What I currently do, is save the date as integer in Excel (for example 44616).

Then substract  21916, then format to date9.

However, this is very unelegant, because the integer value in Excel is not readable.

Another solution is to convert the Excel value in to a string, which also requieres various steps, because just formatting calendar dates to strings in Excel leads to converting them to the integer value prior to changing their format to string (heaven knows why).

Then I can convert the string back to a calender date using import functions of defined substrings for the date components and then use the mdy function to resemble them as a date. 

But, I quote a KOZI FM commercial from the 80's: "Taking it easy is great, but there must be an easier way"

Is there a reliable Excel calendar format, that can be imported streight into SAS EPG  without one of the interfaces messing around?

[AllanBowe]

Hi Gregor - my team have produced a tool for this and it's free for up to 5 users - https://datacontroller.io

Just drag & drop your excel into the browser, and the dates are extracted automatically (and correctly) using our OEM licence of https://sheetjs.com

The underlying (CSV) data is then sent to SAS for review & approve before being applied to your target dataset / CAS table / database.

Feel free to PM or comment if you'd like more details.

/Allan

[Tom]

If you have made sure to enter only dates into a column that should have dates in the Excel file then SAS will read the file properly.

Are you perhaps letting Excel open a text file, like a CSV file, without taking control of how it interprets the text?  That can lead to columns that have a mix of date and character strings.  For example if the CSV file has strings in MDY order and Excel expects dates to appear in DMY order then a value like 12/31/2022 will be stored as a string in the cell instead of a date.

If you cannot fix the Excel file to only have actual date values then you can usually fix the problem on the SAS side.  When SAS sees a column with mixed numbers and character strings it makes a character variable.  When that happens the actual date values a converted to a string that represents the number that Excel uses for that date.  So strings like the '44616' you mentioned.  But the character strings, like the '12/31/2022' in my example, will come over as is.

Here is an example of a data step that convert a variable named DATE that ended up as character in SAS.

data want;
  set have;
  if length(date)=5 then date_number = input(date,32.) + '30DEC1899'd ;
  else date_number = input(date,mmddyy10.);
  format date_number yymmdd10.;
run;

And you know for a fact (but how can you be sure?) that Excel has switched the Month and Day then perhaps make the logic a little more complex.

data want;
  set have;
  if length(date)=5 then do;
    date_number = input(date,32.) + '30DEC1899'd ;
    if month(date_number) <= 12 and day(date_number) <=12 then
      date_number=mdy(day(date_number),month(date_number),year(date_number))
    ;
  end;
  else date_number = input(date,mmddyy10.);
  format date_number yymmdd10.;
run;

PS If you display and enter dates in YMD order then you will have fewer issues with Excel (or SAS or humans) thinking that May first is the Fifth of January.

[Gregor]

Thanks a lot for your reply. At least, it is good to know that there is no easy solution to the problem. I was hoping to be able to simply have a date format in Excel, maybe 2022-14-01, or whatever, which could be detected safely by SAS with settings made in SAS. It is frustrating to see that there seems to be no way to align the two programs, where the issue is quite basic.
I like your approach for dealing with various possible formats and detecting them. I guess that's what I will be heading at with my macros.

[Gregor · Accepted solution]

A collegue provided a cool solution that works for Excel.
Change Excel date column format to user defined YYYY-MM-DD (in German Excel even if set to English JJJJ-MM-TT)
In the EPG import step modify import format of the date column to E8601DAw., column with = 10.
Output format as desired.

[Tom]

---

@Gregor wrote:
Thanks a lot for your reply. At least, it is good to know that there is no easy solution to the problem. I was hoping to be able to simply have a date format in Excel, maybe 2022-14-01, or whatever, which could be detected safely by SAS with settings made in SAS. It is frustrating to see that there seems to be no way to align the two programs, where the issue is quite basic.
I like your approach for dealing with various possible formats and detecting them. I guess that's what I will be heading at with my macros.

---

The issue is not the transfer from Excel to SAS.  It is the entry of the dates in Excel where months and days could be switched..  If the values are dates in Excel then they will be transferred as dates to SAS.  The exception is if you have dates in the column headers, where SAS is expecting variable names.  Since variable names are strings any dates in such cells will be transferred as strings, usually as the number of days since 1900 that Excel uses.

[Gregor]

The big challange, however, is to find a reliable format that both systems interprete in the same way. Adding an Excel to my EPG session, clicking right, pressing import, always makes SAS suggesting an ANYDATE- format which is a lottery, that often leads to chaos and anarchy. I am very happy about a reliable solution.
I have always been very careful about column headers. That hasn't been my problem. My problem was always dates getting lost in translation.

[Tom]

The Enterprise Guide wizard is a totally different system than actual SAS PROC IMPORT.  Enterprise Guide will open the file and try to convert it to a text file that then sends to SAS along with a program to read the text file.

If you want to avoid that you could try using the FILE UPLOAD tool of EG to move the XLSX file to the SAS server and then use PROC IMPORT to read the file.

[Gregor]

Thanks. I am not sure where to find that tool. I do not have user rights to upload directly to the server.
WORK is possible, though.

[Tom]

@ChrisHemedinger has posted about it many times.  He even published a version you could install yourself if the version of EG is so old it does not already have it.

[ChrisHemedinger]

The Copy Files task (in your task list) has been part of SAS EG since v7.13 -- many years now. You should be able to upload to a path on the server: your home directory (you should have one), or /tmp. It can be a temp location as you need it only long enough to then run PROC IMPORT on the file you've copied.

[TomKari]

I've used ddmmmyyyy for a number of years, so today would be 01Dec2022. I've never had a problem with it, but I haven't tested it exhaustively either.