Read excel files with non-standard format and restructure

[BellaLuna]

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

[PaigeMiller]

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.

[BellaLuna]

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?

[PaigeMiller · Accepted solution]

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;

[BellaLuna]

Thanks a bunch!

[AnnaBrown]

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

[BellaLuna]

Done. Thanks.