I've been using SAS for only 2 years and my next task is rather a complicated one.
Summary of the task: I need to export two columns from multiple excel files (xlsx; ~120 files) into one dataset. Each excel file consititute a record or a case. When imported, I would like for the columns from each record to be a row so that in the new dataset, I'll have each record on a row. I understand that for the latter step, I'll need to transpose.
Steps taken: I know how to import one entire excel file into a SAS dataset. I've looked at articles but they only discussed importing select columns or rows from one excel file -- NOT from multiple files.
What I think needs to be done:
What I'm avoiding (marked with *):
Any coding tips will be helpful, and necessary tweaks to my thinking processes will be appreciated.
I'm currently using SAS Studio at home, the free version, which makes things a little bit more challenging.
Thanks!
Essentially you're looking for a macro.
For importing the data from Excel, are the values always in the same location? You can specify the range in Proc Import. Do you have a list of the files or do you need to generate it?
Get it working for one file and then move on. I disagree with your last set of arguments, its probably easier to import each file, transpose and append to the final file. Make sure to clean up between loops.
Here's a rough sketch to get you started. Once you get it working, wrap it in %macro/%mend and then you can use CALL EXECUTE to iterate over all your files.
%let file2imp=myfile.xlsx;
proc import out=temp1 datafile="/folders/myfolders/&file2imp" dbms=excel replace; range='Sheet1$A4:G4'; run;
*Transpose here;
proc transpose data=temp1 out=temp2;
by id;
run;
*Append to table;
proc append base=want data=temp2;
run;
*clean up;
proc sql;
drop table temp1, temp2;
quit;
*repeat;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.