02-13-2016 09:06 AM
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.
02-13-2016 09:17 AM
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;