Help using Base SAS procedures

Reading in an Oddly Formatted Excel File in SAS

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 9
Accepted Solution

Reading in an Oddly Formatted Excel File in SAS

I have some oddly formatted data from an excel spread sheet I'm trying to read in. A sample of the excel file I'm trying to read in is below. So in particular there are two challenges. I would like to ignore the title and start reading in and get names from the top header: Date, A, B, C, D, E, F, Time/Date. This is an sample of the spread sheet:

TITLE
DateABCDEFTime
Date
January
1/20/2004Company A2:00 p.m.ConnecticutNA4,000400,001/26/04, 9:00 a.m.
February
2/12/2004Company B11:32 a.m.San FranciscoNA1,000 340,0003/01/04, 8:50 a.m.
2/28/2004CompanyC 1:00 pm New York NA NA NA
None

The Date column is particularly tricky for me because it has January and February, etc followed by MM/DD/YYYY format beneath it. How can I just read in the MM/DD/YYYY, and ignore the January, February, March, etc or place it side by side while reading in such as  January 1/20/2004, in separate columns, and then the same for February, etc. So When reading in I would like to see something like

MonthDateABCDEFTime/Date
January1/20/2004Company A2:00 p.m.ConnecticutNA4,000400,001/26/04, 9:00 a.m.
February2/12/2004Company B11:32 a.m.San FranciscoNA1,000 340,0003/01/04, 8:50 a.m.
February 2/28/2004CompanyC 1:00 pm New York NA NA NA

Any suggestions?

I'm using this code to begin reading the file in:

PROC IMPORT OUT= WORK.Out

            DATAFILE= "C:\Users\Desktop\Data\Excel1.xls"

            DBMS=EXCEL REPLACE;

     RANGE="Sheet1$";

     GETNAMES=YES;

     MIXED=NO;

     SCANTEXT=YES;

     USEDATE=YES;

     SCANTIME=YES;

RUN;


Accepted Solutions
Solution
‎12-04-2013 10:31 AM
Super User
Posts: 17,963

Re: Reading in an Oddly Formatted Excel File in SAS

I don't think there's an easy way in any language Smiley Happy

There are ways though..

View solution in original post


All Replies
Super User
Posts: 5,260

Re: Reading in an Oddly Formatted Excel File in SAS

Reading Excel files like this is like reading reports.

The best option if available is to try to get a more clean layout from your source (this is to done on a regular basis right?, otherwise fix the Excel manually first).

Is the month name in a separate cell/row?

Either way, import, and then do some data step programming to fix it in the way you like it.

Data never sleeps
Occasional Contributor
Posts: 9

Re: Reading in an Oddly Formatted Excel File in SAS

Yeah, I don't mind fixing the excel manually except. I have a several files that I would have to do so, it seems being able to read in the native format with sas would be the best strategy. The Time and Date are in a separate cell/row.

Super User
Posts: 17,963

Re: Reading in an Oddly Formatted Excel File in SAS

Is the data the exact same in each file, ie same row/same column? If so you could do it "manually" once using DDE and then run for each file.

Still a pain though.

Occasional Contributor
Posts: 9

Re: Reading in an Oddly Formatted Excel File in SAS

So I guess there's no easy ways to do this in sas?

Solution
‎12-04-2013 10:31 AM
Super User
Posts: 17,963

Re: Reading in an Oddly Formatted Excel File in SAS

I don't think there's an easy way in any language Smiley Happy

There are ways though..

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 241 views
  • 0 likes
  • 3 in conversation