BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
BellaLuna
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26

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;
--
Paige Miller

View solution in original post

6 REPLIES 6
PaigeMiller
Diamond | Level 26

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.

--
Paige Miller
BellaLuna
Fluorite | Level 6
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
Diamond | Level 26

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;
--
Paige Miller
AnnaBrown
Community Manager

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


Join us for SAS Community Trivia
SAS Bowl XXIX, The SAS Hackathon
Wednesday, March 8, 2023, at 10 AM ET | #SASBowl

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

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