Import select columns from multiple excel files into one dataset

[jmono]

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: 

1. Write a code to reference the folder where the excel files are stored.
2. Write a code to extract the two columns of interest from the first excel file and store into a new dataset.
3. Then reiterate this code to continue extracting from the next files until it gets to the last file.
4. Make sure to flag those files that have been imported.
5. The new dataset will now contain the following columns: ID-1, Code-1, ID-2, Code-2, ID-3, Code-3, ... Therefore, next step is to transpose.

What I'm avoiding (marked with *):

1. Extract both columns of interest from each excel file into a dataset. This will create ~120 datasets*.
2. Transpose each of the new datasets. 
3. Merge all new datasets. 
4. Delete the ~120 datasets.

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!

[Reeza]

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;