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;
... View more