12-03-2013 01:59 AM
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:
|1/20/2004||Company A||2:00 p.m.||Connecticut||NA||4,000||400,00||1/26/04, 9:00 a.m.|
|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|
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
|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|
I'm using this code to begin reading the file in:
PROC IMPORT OUT= WORK.Out
12-03-2013 12:29 PM
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.
12-03-2013 06:00 PM
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.
12-03-2013 06:11 PM
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.
Need further help from the community? Please ask a new question.