import multiple excel sheets in SAS

[saraphdnz]

Hi there - I need to import multiple sheets from one excel workbook "Transposednew" into SAS as separate SAS data files. Excel workbook "Transposednew" contains 316 sheets with name Jan1990, Feb1990,....Dec2016. Is there any easy way to pull all sheets in SAS ? Regards, Sara

%web_drop_table(myfiles.Apr1991);
FILENAME REFFILE 'H:/Paper 3 SAS/Transposednew.xlsx';
PROC IMPORT DATAFILE=REFFILE
	DBMS=XLSX
	OUT=myfiles.Apr1991;
	GETNAMES=YES;
	SHEET="Apr1991";
RUN;
PROC CONTENTS DATA=myfiles.Apr1991; RUN; 


%web_open_table(myfiles.Apr1991);

[Kurt_Bremser]

Create a library reference, and use proc copy:

libname inexcel xlsx 'H:/Paper 3 SAS/Transposednew.xlsx';

proc copy in=inexcel out=myfiles;
run;

[Shmuel · Accepted solution]

Use next code to loop all sheets:

%let months = JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC;
%macro ex;
%do year = 1990 %to 1992; /* adapt years as need */
    %do i=1 %to 12;
        %let file = %scan(&months,&i)&year;
        %put FILE=&file;
        
        /* enter here your code using &file instead Apr1991 */
        
    %end;
%end;
%mend ex;
%ex;

Pay attention - enter two dots to use:  

myfiles..&file;

[saraphdnz]

Hello Shmuel,

Thanks heaps for your help and code, much appreciated.

I am able to get all my sheets in SAS but I used single dot to import the files as when I used double dot it gave me below error (I use SAS studio).

myfiles.&file;

Regards,

Sara

[Kurt_Bremser]

Since myfiles is not a macro variable, only one dot is needed.

Please post the whole log of a failed step by copy/pasting the log text into a window opened with </>. No need to complicate things by taking screen shots.

[saraphdnz]

Noted. Below is log

 
 1          OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 72         
 73         /* Macro for multiple sheet*/
 74         
 75         %let months = JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC;
 76         %macro ex;
 77         %do year = 1990 %to 1992; /* adapt years as need */
 78             %do i=1 %to 12;
 79                 %let file = %scan(&months,&i)&year;
 80                 %put FILE=&file;
 81         
 82                 /* enter here your code using &file instead Apr1991 */
 83         
 84         
 85                %web_drop_table(myfiles..&file);
 86                 FILENAME REFFILE 'H:/Paper 3 SAS/Transposednew.xlsx';
 87         
 88         PROC IMPORT DATAFILE=REFFILE
 89         DBMS=XLSX
 90         OUT= myfiles..&file;
 91         GETNAMES=YES;
 92         SHEET="&file";
 93         RUN;
 94         
 95         PROC CONTENTS DATA=myfiles.&file; RUN;
 96         %web_open_table(myfiles..&file);
 97         
 98         /* sheet code ended*/
 99         
 100            %end;
 101        %end;
 102        %mend ex;
 103        %ex;
 FILE=JAN1990
 ERROR: "MYFILES." is not a valid name.
 NOTE: PROCEDURE IMPORT used (Total process time):
       real time           0.00 seconds
       cpu time            0.00 seconds
       
 ERROR: Invalid data set name myfiles..
 NOTE: The SAS System stopped processing this step because of errors.
 NOTE: Line generated by the macro variable "FILE".
 103        .JAN1990
            __
            22
            200

 
 ERROR 22-322: Syntax error, expecting one of the following: ;, (, DATAFILE, DATATABLE, DBMS, DEBUG, 
               FILE, OUT, REPLACE, TABLE, _DEBUG_.  
 
 ERROR 200-322: The symbol is not recognized and will be ignored.
 
 
 
 NOTE: PROCEDURE CONTENTS used (Total process time):
       real time           1.53 seconds
       cpu time            1.42 seconds
       

 
 FILE=FEB1990
 ERROR: "MYFILES." is not a valid name.
 NOTE: PROCEDURE IMPORT used (Total process time):
       real time           0.00 seconds
       cpu time            0.00 seconds
       
 ERROR: Invalid data set name myfiles..
 NOTE: The SAS System stopped processing this step because of errors.
 NOTE: Line generated by the macro variable "FILE".
 103        .FEB1990
            __
            22
            200
 
 ERROR 22-322: Syntax error, expecting one of the following: ;, (, DATAFILE, DATATABLE, DBMS, DEBUG, 
               FILE, OUT, REPLACE, TABLE, _DEBUG_.  
 
 ERROR 200-322: The symbol is not recognized and will be ignored.
 
 
 
 NOTE: PROCEDURE CONTENTS used (Total process time):
       real time           1.44 seconds
       cpu time            1.26 seconds
       

 
 FILE=MAR1990
 ERROR: "MYFILES." is not a valid name.
 NOTE: PROCEDURE IMPORT used (Total process time):
       real time           0.00 seconds
       cpu time            0.00 seconds
       
 ERROR: Invalid data set name myfiles..
 NOTE: The SAS System stopped processing this step because of errors.
 NOTE: Line generated by the macro variable "FILE".
 103        .MAR1990
            __
            22
            200
 
 ERROR 22-322: Syntax error, expecting one of the following: ;, (, DATAFILE, DATATABLE, DBMS, DEBUG, 
               FILE, OUT, REPLACE, TABLE, _DEBUG_.  
 
 ERROR 200-322: The symbol is not recognized and will be ignored.
 
 
 
 NOTE: PROCEDURE CONTENTS used (Total process time):
       real time           1.43 seconds
       cpu time            1.28 seconds
       

 
 FILE=APR1990
 ERROR: "MYFILES." is not a valid name.
 NOTE: PROCEDURE IMPORT used (Total process time):
       real time           0.00 seconds
       cpu time            0.00 seconds
       
 ERROR: Invalid data set name myfiles..
 NOTE: The SAS System stopped processing this step because of errors.
 NOTE: Line generated by the macro variable "FILE".
 103        .APR1990
            __
            22
            200
 
 ERROR 22-322: Syntax error, expecting one of the following: ;, (, DATAFILE, DATATABLE, DBMS, DEBUG, 
               FILE, OUT, REPLACE, TABLE, _DEBUG_.  
 
 ERROR 200-322: The symbol is not recognized and will be ignored.
 
 
 
 NOTE: PROCEDURE CONTENTS used (Total process time):
       real time           1.44 seconds
       cpu time            1.39 seconds
       

 
 FILE=MAY1990
 ERROR: "MYFILES." is not a valid name.
 NOTE: PROCEDURE IMPORT used (Total process time):
       real time           0.00 seconds
       cpu time            0.00 seconds
       
 ERROR: Invalid data set name myfiles..
 NOTE: The SAS System stopped processing this step because of errors.
 NOTE: Line generated by the macro variable "FILE".
 103        .MAY1990
            __
            22
            200
 
 ERROR 22-322: Syntax error, expecting one of the following: ;, (, DATAFILE, DATATABLE, DBMS, DEBUG, 
               FILE, OUT, REPLACE, TABLE, _DEBUG_.  
 
 ERROR 200-322: The symbol is not recognized and will be ignored.
 
 
 
 NOTE: PROCEDURE CONTENTS used (Total process time):
       real time           1.36 seconds
       cpu time            1.32 seconds
       

 
 FILE=JUN1990
 ERROR: "MYFILES." is not a valid name.
 NOTE: PROCEDURE IMPORT used (Total process time):
       real time           0.00 seconds
       cpu time            0.00 seconds
       
 ERROR: Invalid data set name myfiles..
 NOTE: The SAS System stopped processing this step because of errors.
 NOTE: Line generated by the macro variable "FILE".
 103        .JUN1990
            __
            22
            200
 
 ERROR 22-322: Syntax error, expecting one of the following: ;, (, DATAFILE, DATATABLE, DBMS, DEBUG, 
               FILE, OUT, REPLACE, TABLE, _DEBUG_.  
 
 ERROR 200-322: The symbol is not recognized and will be ignored.
 
 
 
 NOTE: PROCEDURE CONTENTS used (Total process time):
       real time           1.44 seconds
       cpu time            1.39 seconds
       

 
 FILE=JUL1990
 ERROR: "MYFILES." is not a valid name.
 NOTE: PROCEDURE IMPORT used (Total process time):
       real time           0.00 seconds
       cpu time            0.01 seconds
       
 ERROR: Invalid data set name myfiles..
 NOTE: The SAS System stopped processing this step because of errors.
 NOTE: Line generated by the macro variable "FILE".
 103        .JUL1990
            __
            22
            200
 
 ERROR 22-322: Syntax error, expecting one of the following: ;, (, DATAFILE, DATATABLE, DBMS, DEBUG, 
               FILE, OUT, REPLACE, TABLE, _DEBUG_.  
 
 ERROR 200-322: The symbol is not recognized and will be ignored.
 
 
 
 NOTE: PROCEDURE CONTENTS used (Total process time):
       real time           1.41 seconds
       cpu time            1.37 seconds
       

 
 FILE=AUG1990
 ERROR: "MYFILES." is not a valid name.
 NOTE: PROCEDURE IMPORT used (Total process time):
       real time           0.00 seconds
       cpu time            0.00 seconds
       
 ERROR: Invalid data set name myfiles..
 NOTE: The SAS System stopped processing this step because of errors.
 NOTE: Line generated by the macro variable "FILE".
 103        .AUG1990
            __
            22
            200
 
 ERROR 22-322: Syntax error, expecting one of the following: ;, (, DATAFILE, DATATABLE, DBMS, DEBUG, 
               FILE, OUT, REPLACE, TABLE, _DEBUG_.  
 
 ERROR 200-322: The symbol is not recognized and will be ignored.
 
 
 
 NOTE: PROCEDURE CONTENTS used (Total process time):
       real time           1.46 seconds
       cpu time            1.35 seconds
       

 
 FILE=SEP1990
 ERROR: "MYFILES." is not a valid name.
 NOTE: PROCEDURE IMPORT used (Total process time):
       real time           0.00 seconds
       cpu time            0.00 seconds
       
 ERROR: Invalid data set name myfiles..
 NOTE: The SAS System stopped processing this step because of errors.
 NOTE: Line generated by the macro variable "FILE".
 103        .SEP1990
            __
            22
            200
 
 ERROR 22-322: Syntax error, expecting one of the following: ;, (, DATAFILE, DATATABLE, DBMS, DEBUG, 
               FILE, OUT, REPLACE, TABLE, _DEBUG_.  
 
 ERROR 200-322: The symbol is not recognized and will be ignored.
 
 
 
 NOTE: PROCEDURE CONTENTS used (Total process time):
       real time           1.48 seconds
       cpu time            1.42 seconds
       

 
 FILE=OCT1990
 ERROR: "MYFILES." is not a valid name.
 NOTE: PROCEDURE IMPORT used (Total process time):
       real time           0.00 seconds
       cpu time            0.00 seconds
       
 ERROR: Invalid data set name myfiles..
 NOTE: The SAS System stopped processing this step because of errors.
 NOTE: Line generated by the macro variable "FILE".
 103        .OCT1990
            __
            22
            200
 
 ERROR 22-322: Syntax error, expecting one of the following: ;, (, DATAFILE, DATATABLE, DBMS, DEBUG, 
               FILE, OUT, REPLACE, TABLE, _DEBUG_.  
 
 ERROR 200-322: The symbol is not recognized and will be ignored.
 
 
 
 NOTE: PROCEDURE CONTENTS used (Total process time):
       real time           1.49 seconds
       cpu time            1.43 seconds
       

 
 FILE=NOV1990
 ERROR: "MYFILES." is not a valid name.
 NOTE: PROCEDURE IMPORT used (Total process time):
       real time           0.00 seconds
       cpu time            0.00 seconds
       
 ERROR: Invalid data set name myfiles..
 NOTE: The SAS System stopped processing this step because of errors.
 NOTE: Line generated by the macro variable "FILE".
 103        .NOV1990
            __
            22
            200
 
 ERROR 22-322: Syntax error, expecting one of the following: ;, (, DATAFILE, DATATABLE, DBMS, DEBUG, 
               FILE, OUT, REPLACE, TABLE, _DEBUG_.  
 
 ERROR 200-322: The symbol is not recognized and will be ignored.
 
 
 
 NOTE: PROCEDURE CONTENTS used (Total process time):
       real time           1.55 seconds
       cpu time            1.50 seconds
       

 
 FILE=DEC1990
 ERROR: "MYFILES." is not a valid name.
 NOTE: PROCEDURE IMPORT used (Total process time):
       real time           0.00 seconds
       cpu time            0.00 seconds
       
 ERROR: Invalid data set name myfiles..
 NOTE: The SAS System stopped processing this step because of errors.
 NOTE: Line generated by the macro variable "FILE".
 103        .DEC1990
            __
            22
            200
 
 ERROR 22-322: Syntax error, expecting one of the following: ;, (, DATAFILE, DATATABLE, DBMS, DEBUG, 
               FILE, OUT, REPLACE, TABLE, _DEBUG_.  
 
 ERROR 200-322: The symbol is not recognized and will be ignored.
 
 
 
 NOTE: PROCEDURE CONTENTS used (Total process time):
       real time           1.60 seconds
       cpu time            1.56 seconds
       

 
 FILE=JAN1991
 ERROR: "MYFILES." is not a valid name.
 NOTE: PROCEDURE IMPORT used (Total process time):
       real time           0.00 seconds
       cpu time            0.00 seconds
       
 ERROR: Invalid data set name myfiles..
 NOTE: The SAS System stopped processing this step because of errors.
 NOTE: Line generated by the macro variable "FILE".
 103        .JAN1991
            __
            22
            200
 
 ERROR 22-322: Syntax error, expecting one of the following: ;, (, DATAFILE, DATATABLE, DBMS, DEBUG, 
               FILE, OUT, REPLACE, TABLE, _DEBUG_.  
 
 ERROR 200-322: The symbol is not recognized and will be ignored.
 
 
 
 NOTE: PROCEDURE CONTENTS used (Total process time):
       real time           1.55 seconds
       cpu time            1.48 seconds
       

 
 FILE=FEB1991
 ERROR: "MYFILES." is not a valid name.
 NOTE: PROCEDURE IMPORT used (Total process time):
       real time           0.00 seconds
       cpu time            0.00 seconds
       
 ERROR: Invalid data set name myfiles..
 NOTE: The SAS System stopped processing this step because of errors.
 NOTE: Line generated by the macro variable "FILE".
 103        .FEB1991
            __
            22
            200
 
 ERROR 22-322: Syntax error, expecting one of the following: ;, (, DATAFILE, DATATABLE, DBMS, DEBUG, 
               FILE, OUT, REPLACE, TABLE, _DEBUG_.  
 
 ERROR 200-322: The symbol is not recognized and will be ignored.
 
 
 
 NOTE: PROCEDURE CONTENTS used (Total process time):
       real time           1.54 seconds
       cpu time            1.51 seconds
       

 
 FILE=MAR1991
 ERROR: "MYFILES." is not a valid name.
 NOTE: PROCEDURE IMPORT used (Total process time):
       real time           0.00 seconds
       cpu time            0.00 seconds
       
 ERROR: Invalid data set name myfiles..
 NOTE: The SAS System stopped processing this step because of errors.
 NOTE: Line generated by the macro variable "FILE".
 103        .MAR1991
            __
            22
            200
 
 ERROR 22-322: Syntax error, expecting one of the following: ;, (, DATAFILE, DATATABLE, DBMS, DEBUG, 
               FILE, OUT, REPLACE, TABLE, _DEBUG_.  
 
 ERROR 200-322: The symbol is not recognized and will be ignored.
 
 
 
 NOTE: PROCEDURE CONTENTS used (Total process time):
       real time           1.54 seconds
       cpu time            1.50 seconds
       

 
 FILE=APR1991
 ERROR: "MYFILES." is not a valid name.
 NOTE: PROCEDURE IMPORT used (Total process time):
       real time           0.00 seconds
       cpu time            0.00 seconds
       
 ERROR: Invalid data set name myfiles..
 NOTE: The SAS System stopped processing this step because of errors.
 NOTE: Line generated by the macro variable "FILE".
 103        .APR1991
            __
            22
            200
 
 ERROR 22-322: Syntax error, expecting one of the following: ;, (, DATAFILE, DATATABLE, DBMS, DEBUG, 
               FILE, OUT, REPLACE, TABLE, _DEBUG_.  
 
 ERROR 200-322: The symbol is not recognized and will be ignored.
 
 
 
 NOTE: PROCEDURE CONTENTS used (Total process time):
       real time           1.56 seconds
       cpu time            1.46 seconds
       

 
 FILE=MAY1991
 ERROR: "MYFILES." is not a valid name.
 NOTE: PROCEDURE IMPORT used (Total process time):
       real time           0.00 seconds
       cpu time            0.00 seconds
       
 ERROR: Invalid data set name myfiles..
 NOTE: The SAS System stopped processing this step because of errors.
 NOTE: Line generated by the macro variable "FILE".
 103        .MAY1991
            __
            22
            200
 
 ERROR 22-322: Syntax error, expecting one of the following: ;, (, DATAFILE, DATATABLE, DBMS, DEBUG, 
               FILE, OUT, REPLACE, TABLE, _DEBUG_.  
 
 ERROR 200-322: The symbol is not recognized and will be ignored.
 
 
 
 NOTE: PROCEDURE CONTENTS used (Total process time):
       real time           1.66 seconds
       cpu time            1.56 seconds
       

 
 FILE=JUN1991
 ERROR: "MYFILES." is not a valid name.
 NOTE: PROCEDURE IMPORT used (Total process time):
       real time           0.00 seconds
       cpu time            0.00 seconds
       
 ERROR: Invalid data set name myfiles..
 NOTE: The SAS System stopped processing this step because of errors.
 NOTE: Line generated by the macro variable "FILE".
 103        .JUN1991
            __
            22
            200
 
 ERROR 22-322: Syntax error, expecting one of the following: ;, (, DATAFILE, DATATABLE, DBMS, DEBUG, 
               FILE, OUT, REPLACE, TABLE, _DEBUG_.  
 
 ERROR 200-322: The symbol is not recognized and will be ignored.
 
 
 
 NOTE: PROCEDURE CONTENTS used (Total process time):
       real time           1.64 seconds
       cpu time            1.59 seconds
       

 
 FILE=JUL1991
 ERROR: "MYFILES." is not a valid name.
 NOTE: PROCEDURE IMPORT used (Total process time):
       real time           0.00 seconds
       cpu time            0.00 seconds
       
 ERROR: Invalid data set name myfiles..
 NOTE: The SAS System stopped processing this step because of errors.
 NOTE: Line generated by the macro variable "FILE".
 103        .JUL1991
            __
            22
            200
 
 ERROR 22-322: Syntax error, expecting one of the following: ;, (, DATAFILE, DATATABLE, DBMS, DEBUG, 
               FILE, OUT, REPLACE, TABLE, _DEBUG_.  
 
 ERROR 200-322: The symbol is not recognized and will be ignored.
 
 
 
 NOTE: PROCEDURE CONTENTS used (Total process time):
       real time           1.69 seconds
       cpu time            1.65 seconds
       

 
 FILE=AUG1991
 ERROR: "MYFILES." is not a valid name.
 NOTE: PROCEDURE IMPORT used (Total process time):
       real time           0.00 seconds
       cpu time            0.00 seconds
       
 ERROR: Invalid data set name myfiles..
 NOTE: The SAS System stopped processing this step because of errors.
 NOTE: Line generated by the macro variable "FILE".
 103        .AUG1991
            __
            22
            200
 
 ERROR 22-322: Syntax error, expecting one of the following: ;, (, DATAFILE, DATATABLE, DBMS, DEBUG, 
               FILE, OUT, REPLACE, TABLE, _DEBUG_.  
 
 ERROR 200-322: The symbol is not recognized and will be ignored.
 
 
 
 NOTE: PROCEDURE CONTENTS used (Total process time):
       real time           1.65 seconds
       cpu time            1.62 seconds
       

 
 FILE=SEP1991
 ERROR: "MYFILES." is not a valid name.
 NOTE: PROCEDURE IMPORT used (Total process time):
       real time           0.00 seconds
       cpu time            0.00 seconds
       
 ERROR: Invalid data set name myfiles..
 NOTE: The SAS System stopped processing this step because of errors.
 NOTE: Line generated by the macro variable "FILE".
 103        .SEP1991
            __
            22
            200
 
 ERROR 22-322: Syntax error, expecting one of the following: ;, (, DATAFILE, DATATABLE, DBMS, DEBUG, 
               FILE, OUT, REPLACE, TABLE, _DEBUG_.  
 
 ERROR 200-322: The symbol is not recognized and will be ignored.
 
 
 
 NOTE: PROCEDURE CONTENTS used (Total process time):
       real time           1.72 seconds
       cpu time            1.70 seconds
       

 
 FILE=OCT1991
 ERROR: "MYFILES." is not a valid name.
 NOTE: PROCEDURE IMPORT used (Total process time):
       real time           0.00 seconds
       cpu time            0.00 seconds
       
 ERROR: Invalid data set name myfiles..
 NOTE: The SAS System stopped processing this step because of errors.
 NOTE: Line generated by the macro variable "FILE".
 103        .OCT1991
            __
            22
            200
 
 ERROR 22-322: Syntax error, expecting one of the following: ;, (, DATAFILE, DATATABLE, DBMS, DEBUG, 
               FILE, OUT, REPLACE, TABLE, _DEBUG_.  
 
 ERROR 200-322: The symbol is not recognized and will be ignored.
 
 
 
 NOTE: PROCEDURE CONTENTS used (Total process time):
       real time           1.79 seconds
       cpu time            1.59 seconds
       

 
 FILE=NOV1991
 ERROR: "MYFILES." is not a valid name.
 NOTE: PROCEDURE IMPORT used (Total process time):
       real time           0.00 seconds
       cpu time            0.00 seconds
       
 ERROR: Invalid data set name myfiles..
 NOTE: The SAS System stopped processing this step because of errors.
 NOTE: Line generated by the macro variable "FILE".
 103        .NOV1991
            __
            22
            200
 
 ERROR 22-322: Syntax error, expecting one of the following: ;, (, DATAFILE, DATATABLE, DBMS, DEBUG, 
               FILE, OUT, REPLACE, TABLE, _DEBUG_.  
 
 ERROR 200-322: The symbol is not recognized and will be ignored.
 
 
 
 NOTE: PROCEDURE CONTENTS used (Total process time):
       real time           1.77 seconds
       cpu time            1.73 seconds
       

 
 FILE=DEC1991
 ERROR: "MYFILES." is not a valid name.
 NOTE: PROCEDURE IMPORT used (Total process time):
       real time           0.00 seconds
       cpu time            0.00 seconds
       
 ERROR: Invalid data set name myfiles..
 NOTE: The SAS System stopped processing this step because of errors.
 NOTE: Line generated by the macro variable "FILE".
 103        .DEC1991
            __
            22
            200
 
 ERROR 22-322: Syntax error, expecting one of the following: ;, (, DATAFILE, DATATABLE, DBMS, DEBUG, 
               FILE, OUT, REPLACE, TABLE, _DEBUG_.  
 
 ERROR 200-322: The symbol is not recognized and will be ignored.
 
 
 
 NOTE: PROCEDURE CONTENTS used (Total process time):
       real time           1.71 seconds
       cpu time            1.68 seconds
       

 
 FILE=JAN1992
 ERROR: "MYFILES." is not a valid name.
 NOTE: PROCEDURE IMPORT used (Total process time):
       real time           0.00 seconds
       cpu time            0.00 seconds
       
 ERROR: Invalid data set name myfiles..
 NOTE: The SAS System stopped processing this step because of errors.
 NOTE: Line generated by the macro variable "FILE".
 103        .JAN1992
            __
            22
            200
 
 ERROR 22-322: Syntax error, expecting one of the following: ;, (, DATAFILE, DATATABLE, DBMS, DEBUG, 
               FILE, OUT, REPLACE, TABLE, _DEBUG_.  
 
 ERROR 200-322: The symbol is not recognized and will be ignored.
 
 
 
 NOTE: PROCEDURE CONTENTS used (Total process time):
       real time           1.74 seconds
       cpu time            1.70 seconds
       

 
 FILE=FEB1992
 ERROR: "MYFILES." is not a valid name.
 NOTE: PROCEDURE IMPORT used (Total process time):
       real time           0.00 seconds
       cpu time            0.00 seconds
       
 ERROR: Invalid data set name myfiles..
 NOTE: The SAS System stopped processing this step because of errors.
 NOTE: Line generated by the macro variable "FILE".
 103        .FEB1992
            __
            22
            200
 
 ERROR 22-322: Syntax error, expecting one of the following: ;, (, DATAFILE, DATATABLE, DBMS, DEBUG, 
               FILE, OUT, REPLACE, TABLE, _DEBUG_.  
 
 ERROR 200-322: The symbol is not recognized and will be ignored.
 
 
 
 NOTE: PROCEDURE CONTENTS used (Total process time):
       real time           1.77 seconds
       cpu time            1.67 seconds
       

 
 FILE=MAR1992
 ERROR: "MYFILES." is not a valid name.
 NOTE: PROCEDURE IMPORT used (Total process time):
       real time           0.00 seconds
       cpu time            0.00 seconds
       
 ERROR: Invalid data set name myfiles..
 NOTE: The SAS System stopped processing this step because of errors.
 NOTE: Line generated by the macro variable "FILE".
 103        .MAR1992
            __
            22
            200
 
 ERROR 22-322: Syntax error, expecting one of the following: ;, (, DATAFILE, DATATABLE, DBMS, DEBUG, 
               FILE, OUT, REPLACE, TABLE, _DEBUG_.  
 
 ERROR 200-322: The symbol is not recognized and will be ignored.
 
 
 
 NOTE: PROCEDURE CONTENTS used (Total process time):
       real time           1.74 seconds
       cpu time            1.68 seconds
       

 
 FILE=APR1992
 ERROR: "MYFILES." is not a valid name.
 NOTE: PROCEDURE IMPORT used (Total process time):
       real time           0.00 seconds
       cpu time            0.00 seconds
       
 ERROR: Invalid data set name myfiles..
 NOTE: The SAS System stopped processing this step because of errors.
 NOTE: Line generated by the macro variable "FILE".
 103        .APR1992
            __
            22
            200
 
 ERROR 22-322: Syntax error, expecting one of the following: ;, (, DATAFILE, DATATABLE, DBMS, DEBUG, 
               FILE, OUT, REPLACE, TABLE, _DEBUG_.  
 
 ERROR 200-322: The symbol is not recognized and will be ignored.
 
 
 
 NOTE: PROCEDURE CONTENTS used (Total process time):
       real time           1.68 seconds
       cpu time            1.57 seconds
       

 
 FILE=MAY1992
 ERROR: "MYFILES." is not a valid name.
 NOTE: PROCEDURE IMPORT used (Total process time):
       real time           0.00 seconds
       cpu time            0.00 seconds
       
 ERROR: Invalid data set name myfiles..
 NOTE: The SAS System stopped processing this step because of errors.
 NOTE: Line generated by the macro variable "FILE".
 103        .MAY1992
            __
            22
            200
 
 ERROR 22-322: Syntax error, expecting one of the following: ;, (, DATAFILE, DATATABLE, DBMS, DEBUG, 
               FILE, OUT, REPLACE, TABLE, _DEBUG_.  
 
 ERROR 200-322: The symbol is not recognized and will be ignored.
 
 
 
 NOTE: PROCEDURE CONTENTS used (Total process time):
       real time           1.74 seconds
       cpu time            1.70 seconds
       

 
 FILE=JUN1992
 ERROR: "MYFILES." is not a valid name.
 NOTE: PROCEDURE IMPORT used (Total process time):
       real time           0.00 seconds
       cpu time            0.00 seconds
       
 ERROR: Invalid data set name myfiles..
 NOTE: The SAS System stopped processing this step because of errors.
 NOTE: Line generated by the macro variable "FILE".
 103        .JUN1992
            __
            22
            200
 
 ERROR 22-322: Syntax error, expecting one of the following: ;, (, DATAFILE, DATATABLE, DBMS, DEBUG, 
               FILE, OUT, REPLACE, TABLE, _DEBUG_.  
 
 ERROR 200-322: The symbol is not recognized and will be ignored.
 
 
 
 NOTE: PROCEDURE CONTENTS used (Total process time):
       real time           1.81 seconds
       cpu time            1.78 seconds
       

 
 FILE=JUL1992
 ERROR: "MYFILES." is not a valid name.
 NOTE: PROCEDURE IMPORT used (Total process time):
       real time           0.00 seconds
       cpu time            0.00 seconds
       
 ERROR: Invalid data set name myfiles..
 NOTE: The SAS System stopped processing this step because of errors.
 NOTE: Line generated by the macro variable "FILE".
 103        .JUL1992
            __
            22
            200
 
 ERROR 22-322: Syntax error, expecting one of the following: ;, (, DATAFILE, DATATABLE, DBMS, DEBUG, 
               FILE, OUT, REPLACE, TABLE, _DEBUG_.  
 
 ERROR 200-322: The symbol is not recognized and will be ignored.
 
 
 
 NOTE: PROCEDURE CONTENTS used (Total process time):
       real time           1.76 seconds
       cpu time            1.73 seconds
       

 
 FILE=AUG1992
 ERROR: "MYFILES." is not a valid name.
 NOTE: PROCEDURE IMPORT used (Total process time):
       real time           0.00 seconds
       cpu time            0.00 seconds
       
 ERROR: Invalid data set name myfiles..
 NOTE: The SAS System stopped processing this step because of errors.
 NOTE: Line generated by the macro variable "FILE".
 103        .AUG1992
            __
            22
            200
 
 ERROR 22-322: Syntax error, expecting one of the following: ;, (, DATAFILE, DATATABLE, DBMS, DEBUG, 
               FILE, OUT, REPLACE, TABLE, _DEBUG_.  
 
 ERROR 200-322: The symbol is not recognized and will be ignored.
 
 
 
 NOTE: PROCEDURE CONTENTS used (Total process time):
       real time           1.95 seconds
       cpu time            1.90 seconds
       

 
 FILE=SEP1992
 ERROR: "MYFILES." is not a valid name.
 NOTE: PROCEDURE IMPORT used (Total process time):
       real time           0.00 seconds
       cpu time            0.00 seconds
       
 ERROR: Invalid data set name myfiles..
 NOTE: The SAS System stopped processing this step because of errors.
 NOTE: Line generated by the macro variable "FILE".
 103        .SEP1992
            __
            22
            200
 
 ERROR 22-322: Syntax error, expecting one of the following: ;, (, DATAFILE, DATATABLE, DBMS, DEBUG, 
               FILE, OUT, REPLACE, TABLE, _DEBUG_.  
 
 ERROR 200-322: The symbol is not recognized and will be ignored.
 
 
 
 NOTE: PROCEDURE CONTENTS used (Total process time):
       real time           1.85 seconds
       cpu time            1.75 seconds
       

 
 FILE=OCT1992
 ERROR: "MYFILES." is not a valid name.
 NOTE: PROCEDURE IMPORT used (Total process time):
       real time           0.00 seconds
       cpu time            0.00 seconds
       
 ERROR: Invalid data set name myfiles..
 NOTE: The SAS System stopped processing this step because of errors.
 NOTE: Line generated by the macro variable "FILE".
 103        .OCT1992
            __
            22
            200
 
 ERROR 22-322: Syntax error, expecting one of the following: ;, (, DATAFILE, DATATABLE, DBMS, DEBUG, 
               FILE, OUT, REPLACE, TABLE, _DEBUG_.  
 
 ERROR 200-322: The symbol is not recognized and will be ignored.
 
 
 
 NOTE: PROCEDURE CONTENTS used (Total process time):
       real time           1.88 seconds
       cpu time            1.86 seconds
       

 
 FILE=NOV1992
 ERROR: "MYFILES." is not a valid name.
 NOTE: PROCEDURE IMPORT used (Total process time):
       real time           0.00 seconds
       cpu time            0.00 seconds
       
 ERROR: Invalid data set name myfiles..
 NOTE: The SAS System stopped processing this step because of errors.
 NOTE: Line generated by the macro variable "FILE".
 103        .NOV1992
            __
            22
            200
 
 ERROR 22-322: Syntax error, expecting one of the following: ;, (, DATAFILE, DATATABLE, DBMS, DEBUG, 
               FILE, OUT, REPLACE, TABLE, _DEBUG_.  
 
 ERROR 200-322: The symbol is not recognized and will be ignored.
 
 
 
 NOTE: PROCEDURE CONTENTS used (Total process time):
       real time           1.94 seconds
       cpu time            1.87 seconds
       

 
 FILE=DEC1992
 ERROR: "MYFILES." is not a valid name.
 NOTE: PROCEDURE IMPORT used (Total process time):
       real time           0.00 seconds
       cpu time            0.00 seconds
       
 ERROR: Invalid data set name myfiles..
 NOTE: The SAS System stopped processing this step because of errors.
 NOTE: Line generated by the macro variable "FILE".
 103        .DEC1992
            __
            22
            200
 
 ERROR 22-322: Syntax error, expecting one of the following: ;, (, DATAFILE, DATATABLE, DBMS, DEBUG, 
               FILE, OUT, REPLACE, TABLE, _DEBUG_.  
 
 ERROR 200-322: The symbol is not recognized and will be ignored.
 
 
 
 NOTE: PROCEDURE CONTENTS used (Total process time):
       real time           1.97 seconds
       cpu time            1.90 seconds
       

 
 104        
 105        OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

 118        

in case you need.

[saraphdnz]

I have another query related to the files I imported via the macro.  Not sure, if I need to start a new topic or can ask here.

I need to run simple regression for all these sheets by using a macro.

I have different dependent variables and one independent variable in each file. my dependent variables are in the range of F1, F2, .... F7000 and the variables are not in sequence like my Apr1991 file contain 1312 variable ( from F29  till F6903).

The independent variable is EPU in every file.

At the moment I am running the following regression to get the intercepts - can you also suggest a macro for this program? Regards, Sara

proc reg data=myfiles.Apr1991 noprint outest=myfiles.myestApr1991;
model F29--F6903 = EPU;
run;

[Shmuel]

Usually it is better to open a new query, but in this case

you can just attach the regression part to the import part as one program, inside the macro,

and just replace the name or name suffix with &file as before:

proc reg data=myfiles.&file noprint outest=myfiles.myest&file;

[saraphdnz]

Thanks for the information and code. I am now able to run both program successfully. Here is the model part which I added to pull all variables.  Regards, Sara 

	/* Regression Part*/
	
 proc reg data=myfiles.&file noprint outest=myfiles.myest&file;
 model F: = EPU;