Desktop productivity for business analysts and programmers

EG 4.1 putting F# for Dates imported from Excel

Reply
N/A
Posts: 0

EG 4.1 putting F# for Dates imported from Excel

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!
SAS Employee
Posts: 149

Re: EG 4.1 putting F# for Dates imported from Excel

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
N/A
Posts: 0

Re: EG 4.1 putting F# for Dates imported from Excel

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!
SAS Employee
Posts: 149

Re: EG 4.1 putting F# for Dates imported from Excel

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.
N/A
Posts: 0

Re: EG 4.1 putting F# for Dates imported from Excel

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.
Ask a Question
Discussion stats
  • 4 replies
  • 287 views
  • 0 likes
  • 2 in conversation