topic Re: Reading in an Oddly Formatted Excel File in SAS in SAS Procedures

Reading in an Oddly Formatted Excel File in SAS

steppermotor — Tue, 03 Dec 2013 06:59:47 GMT

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
Date	A	B	C	D	E	F	Time
Date	A	B	C	D	E	F	Date
January
1/20/2004	Company A	2:00 p.m.	Connecticut	NA	4,000	400,00	1/26/04, 9:00 a.m.
February
2/12/2004	Company B	11:32 a.m.	San Francisco	NA	1,000	340,000	3/01/04, 8:50 a.m.
2/28/2004	CompanyC	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

Month	Date	A	B	C	D	E	F	Time/Date
January	1/20/2004	Company A	2:00 p.m.	Connecticut	NA	4,000	400,00	1/26/04, 9:00 a.m.
February	2/12/2004	Company B	11:32 a.m.	San Francisco	NA	1,000	340,000	3/01/04, 8:50 a.m.
February	2/28/2004	CompanyC	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;

Re: Reading in an Oddly Formatted Excel File in SAS

LinusH — Tue, 03 Dec 2013 17:29:09 GMT

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.

Re: Reading in an Oddly Formatted Excel File in SAS

steppermotor — Tue, 03 Dec 2013 23:00:15 GMT

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.

Re: Reading in an Oddly Formatted Excel File in SAS

Reeza — Tue, 03 Dec 2013 23:11:05 GMT

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.

Re: Reading in an Oddly Formatted Excel File in SAS

steppermotor — Wed, 04 Dec 2013 02:00:31 GMT

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

Re: Reading in an Oddly Formatted Excel File in SAS

Reeza — Wed, 04 Dec 2013 15:31:46 GMT

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

There are ways though..