BookmarkSubscribeRSS Feed
deleted_user
Not applicable
I have the EG 4.2 learning guide; trying to import Excel xls file (wouldn't allow xlsx) and dates (specified as line to use as column heading, although the rest of the column is numeric) are coming in as F1, F2, etc. I googled and found a fix, however, when I copy it, the files still have the old date. Not sure what I'm doing wrong; tried looking at the sas code, haven't coded in at least 20 years (ouch!). apologies for what is probably a really dumb question, but I would appreciate any suggestions...

I've tried installing the fix from outside the EG directory, within the EG directory, no luck. Thanks!
4 REPLIES 4
RichardH_sas
SAS Employee
You're right: from EG 4.1 (build into Learning Edition), you won't be able to import .xlsx files. You'd have to do a Save As from Excel to create a .xls file, then import the .xls file.

When I was testing some column headers in EG 4.1, values that were pure numbers (like 25, $70.07, and dates) were brought in as F1, F2, etc. So, check your column headers in Excel to make sure they are text and not numbers. Other column names (5 Test, Test/4) seem to work just fine. It was really the pure numbers that resulted in columns named things like F1.

I walked through a sample import and this is what I saw:

1. File / Open / Data and navigate to the .xls file
2. See worksheets, click the checkbox for one of them
3. Click the second option to open the file as a SAS data set (FYI, you might try the "open the file as is" button too to see if that meets your needs)
4. In Region to import, make sure specify line for column headings is checked and set to the correct line.
5. In Column Options, names should show up as they appear in the Excel file.
6. Import works fine, and I get the appropriate column names.

The only problem notes I saw related to F1, F2 were the following, which don't sound like what you're describing:

http://support.sas.com/kb/16/937.html
http://support.sas.com/kb/36/530.html
deleted_user
Not applicable
Thanks! appreciate the confirmation and follow up immensely.

I was trying to bring the dates in as dates so that I could query based on the date after the data was transposed. I see from the second link in your response, that even with 4.2, I would have to enter the dates again for an existing project. So for now, I will bring the dates as text.

Thanks again!
RichardH_sas
SAS Employee
Dates in the data values (not the first row) are fine. It would just be a date in the column name that could cause issues, since for Excel this is a formatted number. If you had dates as the names of your columns, you could get around this by using the Excel trick of manually adding an apostrophe ' in front of the cell value. So, 01Jan1970 would be a date, but '01Jan1970 would be text to Excel. I think that would do it, though this would involve a manual modification in Excel for each column in question.
deleted_user
Not applicable
Thanks very much for the information; I did add, however, was looking for away to hand manipulate the data prior to processing as little as possible.

Another question; I have three spreadsheets in the excel file; want to run program that will output each one to a separate file; is there a way to specify programmatically? it looks like EG is creating a temp file after I select the spreadsheet, I want to have that embedded in the code.

I then want to use the code generated the first time around for formatting dates) and restricting the number of records that come in.

Thanks for any assistance, very much appreciated.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 960 views
  • 0 likes
  • 2 in conversation