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: 19,869

Re: Reading in an Oddly Formatted Excel File in SAS

Posted in reply to steppermotor

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,438

Re: Reading in an Oddly Formatted Excel File in SAS

Posted in reply to steppermotor

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: 19,869

Re: Reading in an Oddly Formatted Excel File in SAS

Posted in reply to steppermotor

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

Posted in reply to steppermotor

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

Solution
‎12-04-2013 10:31 AM
Super User
Posts: 19,869

Re: Reading in an Oddly Formatted Excel File in SAS

Posted in reply to steppermotor

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
  • 243 views
  • 0 likes
  • 3 in conversation