topic Re: Excel import in SAS Procedures

Excel import

deleted_user — Sun, 09 Mar 2008 14:50:36 GMT

I have data sets in excel that I want to bring into SAS. They are structured like this.

Company Date var1 var2 var3
ABC 1/2/05
2 q 1
3 d 2
4 e 4
DEF 1/2/06
4 e 5
5 e 7

The number of observations per company vary. What I want to do is to spread the company and date information across the corresponding var information.

Re: Excel import

Peter_C — Sun, 09 Mar 2008 22:38:53 GMT

sounds like a minor task for a data step.

Just read a line and hold it; identify it's type and read relevant variables; if it is a company heading "RETAIN" those values; if it is a regular set of variables, "OUTPUT" a data row.

You may want these data in separate tables. The DATA statement allows more than one output data set to be defined. The OUTPUT statement allows you to nominate the table into which the row should be written.

There may even be an example in the manual. Search for "reading hierarchical files". I think that is the terminology for your data structure.

Good Luck

PeterC

Re: Excel import

Cynthia_sas — Mon, 10 Mar 2008 00:00:25 GMT

Hi:
I'm having trouble envisioning what your Excel sheet looks like.

Option 1: 5 columns with variable names in row 1 and company and date on a row by themselves:
[pre]
A B C D E
1 Company Date var1 var2 var3
2 ABC 1/2/05
3 2 q 1
4 3 d 2
5 4 e 4
6 DEF 1/2/06
7 4 e 5
8 5 e 7
[/pre]

Option 2: company name is only on row 1 of the group and var1-var3 have values on the company row:
[pre]
A B C D E
1 Company Date var1 var2 var3
2 ABC 1/2/05 2 q 1
3 3 d 2
4 4 e 4
5 DEF 1/2/06 4 e 5
6 5 e 7
[/pre]

Option 3: 3 columns where column A and Column B are being used differently on every row:
[pre]
A B C
1 ABC 1/2/05
2 2 q 1
3 3 d 2
4 4 e 4
5 DEF 1/2/06
6 4 e 5
7 5 e 7
[/pre]

It doesn't make a LOT of difference, the first 2 options for how the data will be read in probably is going to take a data step program with a RETAIN statement. The 3rd option might require a bit more data manipulation in addition to a RETAIN statement.

cynthia