Thanks for your reply! There is no blank or special character in the path, and all xls files are located in the same directory for sure. I could show you the codes; /* I want to extract the specific rows in Sheet1 for each excel file, and transpose each before merging them together, below is the %macro merge(n) I modified based on your code */ options mprint mlogic symbolgen; %macro merge(n); %do i=1 %to &n; proc import datafile="&&all_files&i" out=one&i dbms=excel replace; sheet="Sheet1$48:54"; getnames=no; run; proc transpose data=one&i out=two&i; id F1; run; %end; data whole; set %do j=1 %to &n; two&j %end; ; run; %mend merge; /* According your codes, data 'file' is made of each excel file's name, but when I ran your code, each cell in the data 'file' is 'C:\Users\WIN7\filename.xls', so I revised the code */ FILENAME excl pipe "dir/b C:\Users\WIN7\*.xls"; run; data all_files; length fl_nm $200 file_name $200; DROP fl_nm; infile excl truncover END=last; input fl_nm; file_name=scan(fl_nm,1,'.'); IF last=1 THEN call SYMPUT("nfiles",put(_n_,4.)); run; %put &nfiles; data _null_; infile excl; input; list; run; data null; set all_files; call symputx('all_files'||put(_n_,1.),file_name,'G'); run; /* When I ran <data null> part, there is an error, but I am pretty sure that my excel filenames contain only allowed character. ERROR: Symbolic variable name ALL_FILES* must contain only letters, digits, and underscores. And data 'null' actually is identical with data 'all_files', I want to ask how it should look like? Finally I ran: options sasautos=work; %merge(2700); Actually I successfully create data ONE1-ONE9, TWO1-TWO9, but after the first 9 files, there is warning: MLOGIC(MERGE): %DO loop index variable I is now 10; loop will iterate again. SYMBOLGEN: && resolves to &. SYMBOLGEN: Macro variable I resolves to 10 WARNING: Apparent symbolic reference ALL_FILES10 not resolved. SYMBOLGEN: Macro variable I resolves to 10 MPRINT(MERGE): proc import datafile="&all_files10" out=one10 dbms=excel replace; MPRINT(MERGE): AEXC; MPRINT(MERGE): sheet="Sheet1$48:54"; MPRINT(MERGE): getnames=no; MPRINT(MERGE): run; ERROR: Unable to open file C:\Users\WIN7\&all_files10.XLS. It does not exist or it is already opened exclusively by another user, or you need permission to view its data. NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE IMPORT used (Total process time): real time 0.23 seconds cpu time 0.14 seconds Could you please look at my codes? There must be some errors but I don't know where it is. Looking forward your reply!
... View more