BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Gregor
Obsidian | Level 7

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?

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Gregor
Obsidian | Level 7
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.

View solution in original post

11 REPLIES 11
AllanBowe
Barite | Level 11

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

/Allan
SAS Challenges - SASensei
MacroCore library for app developers
SAS networking events (BeLux, Germany, UK&I)

Data Workflows, Data Contracts, Data Lineage, Drag & drop excel EUCs to SAS 9 & Viya - Data Controller
DevOps and AppDev on SAS 9 / Viya / Base SAS - SASjs
Tom
Super User Tom
Super User

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
Obsidian | Level 7
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
Obsidian | Level 7
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
Super User Tom
Super User

@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
Obsidian | Level 7
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
Super User Tom
Super User

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
Obsidian | Level 7
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
Super User Tom
Super User

@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
Community Manager

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.

Learn from the Experts! Check out the huge catalog of free sessions in the Ask the Expert webinar series.
TomKari
Onyx | Level 15

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.

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 11 replies
  • 1807 views
  • 1 like
  • 5 in conversation