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
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;
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.
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;
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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.