SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Read excel files with non-standard format and restructure

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 10
Accepted Solution

Read excel files with non-standard format and restructure

[ Edited ]

Hi all,

Greetings!

My problem is, I have hundreds of excel files that look like this: Screen Shot 2015-10-28 at 11.26.14 AM.png

 

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

Screen Shot 2015-10-28 at 11.36.33 AM.png

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


Accepted Solutions
Solution
‎10-28-2015 04:59 PM
Trusted Advisor
Posts: 1,913

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

Posted in reply to BellaLuna

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;

View solution in original post


All Replies
Trusted Advisor
Posts: 1,913

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

Posted in reply to BellaLuna

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.

Occasional Contributor
Posts: 10

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

Posted in reply to PaigeMiller
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?
Solution
‎10-28-2015 04:59 PM
Trusted Advisor
Posts: 1,913

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

Posted in reply to BellaLuna

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;
Occasional Contributor
Posts: 10

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

Posted in reply to PaigeMiller
Thanks a bunch!
Community Manager
Posts: 564

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

Posted in reply to BellaLuna

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

Occasional Contributor
Posts: 10

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

Posted in reply to AnnaBrown
Done. Thanks.
🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 474 views
  • 3 likes
  • 3 in conversation