topic Re: Read excel files with non-standard format and restructure in SAS Data Management

Read excel files with non-standard format and restructure

BellaLuna — Wed, 28 Oct 2015 16:14:29 GMT

Hi all,

Greetings!

My problem is, I have hundreds of excel files that look like this:

I need it eventually to become panel data that look like this:

which is just copy the first few rows and transpose them next to the price column, and then drag to the end to have the same value.

Since I have hundreds of excel files like this, it's not feasible to do it one file after another mannually. I wonder if there's way to program in SAS that can import all the excel files and for each file, structure to the final layout?

Any help will be greatly appreciated!

Best,

Xinxin

Re: Read excel files with non-standard format and restructure

PaigeMiller — Wed, 28 Oct 2015 16:23:48 GMT

Read the first 6 rows into SAS. Transpose this SAS data set. Then read the remaining rows and merge the remaining rows with the transposed data.

Re: Read excel files with non-standard format and restructure

BellaLuna — Wed, 28 Oct 2015 16:31:08 GMT

Thank you! Now I know it's doable. I'm very new to SAS and I apologize for the naiveness of the problem. But would you be willing to write down a somewhat code of the process that I can start to learn and adjust?

Re: Read excel files with non-standard format and restructure

PaigeMiller — Wed, 28 Oct 2015 18:42:41 GMT

Untested code:

proc import datafile="c:\mydata\yourexcelfile.xlsx" dbms=excel out=sasdatasetname;
    getnames=no;
run;
data header;
    set sasdatasetname(obs=6);
run;
proc transpose data=header out=transposed;
    var f2;
    id f1;
run;
data final;
    if _n_=1 then set transposed;
    set sasdatasetname(firstobs=7);
run;

Re: Read excel files with non-standard format and restructure

BellaLuna — Wed, 28 Oct 2015 20:55:06 GMT

Thanks a bunch!

Re: Read excel files with non-standard format and restructure

AnnaBrown — Wed, 28 Oct 2015 20:58:52 GMT

It sounds like you got the help you needed, BellaLuna, that's great! Can you mark which answer worked by clicking "Accept as solution"?

Thanks and look forward to "seeing" more of you here on the community!

Anna

Re: Read excel files with non-standard format and restructure

BellaLuna — Wed, 28 Oct 2015 20:59:45 GMT

Done. Thanks.