Export multiple SAS data files in Excel Macro

saraphdnz — Wed, 15 Apr 2020 23:23:59 GMT

I have around 300 data files under my MYFILES library folder and i am using the following Macro (you provided earlier) with some adjustments. I am able to export all sas data files in my c drive. But I want them in my H drive but unable to figure it out where to put the location. Please advise. Regards, Sara

/* Macro for exporting multiple 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=MYEST&file;

        /* enter code */
       
      libname Myfiles 'H:/Paper 3 SAS/SASFiles'; run;
      
proc export data=myfiles.myest&file
            outfile= estmyest&file
            dbms= xlsx replace;
run;

%let _DATAOUT_MIME_TYPE=xlsx;
%let _DATAOUT_NAME=myest&file.xlsx;

/* %let _DATAOUT_NAME='H:/Paper 3 SAS/SASFiles/Estimates/myest&file.csv';  */

/* Code ended*/
        
    %end;
%end;
%mend ex;
%ex;

 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=MYEST&file;
 81                 /* enter here your code using &file instead Apr1991 */
 82         
 83               libname Myfiles 'H:/Paper 3 SAS/SASFiles'; run;
 84         
 85         proc export data=myfiles.myest&file
 86                     outfile= estmyest&file
 87                     dbms= xlsx replace;
 88         run;
 89         
 90         %let _DATAOUT_MIME_TYPE=xlsx;
 91         %let _DATAOUT_NAME=myest&file.xlsx;
 92         
 93         /* %let _DATAOUT_NAME='H:/Paper 3 SAS/SASFiles/Estimates/myest&file.csv';  */
 94         
 95         /* enter here your code using &file instead Apr1991 */
 96         
 97             %end;
 98         %end;
 99         %mend ex;
 100        %ex;
 FILE=MYESTJAN1990
 NOTE: Libref MYFILES was successfully assigned as follows: 
       Engine:        V9 
       Physical Name: H:\Paper 3 SAS\SASFiles
 
 NOTE: The export data set has 1183 observations and 1189 variables.
 NOTE: "C:\Users\em9058\ESTMYESTJAN1990.xlsx" file was successfully created.
 NOTE: PROCEDURE EXPORT used (Total process time):
       real time           0.19 seconds
       cpu time            0.09 seconds
       

 
 FILE=MYESTFEB1990
 NOTE: Libref MYFILES was successfully assigned as follows: 
       Engine:        V9 
       Physical Name: H:\Paper 3 SAS\SASFiles
 
 NOTE: The export data set has 1187 observations and 1193 variables.
 NOTE: "C:\Users\em9058\ESTMYESTFEB1990.xlsx" file was successfully created.
 NOTE: PROCEDURE EXPORT used (Total process time):
       real time           0.13 seconds
       cpu time            0.09 seconds
       
 
 FILE=MYESTMAR1990
 NOTE: Libref MYFILES was successfully assigned as follows: 
       Engine:        V9 
       Physical Name: H:\Paper 3 SAS\SASFiles
 
 NOTE: The export data set has 1199 observations and 1205 variables.
 NOTE: "C:\Users\em9058\ESTMYESTMAR1990.xlsx" file was successfully created.
 NOTE: PROCEDURE EXPORT used (Total process time):
       real time           0.13 seconds
       cpu time            0.07 seconds
       

 
 FILE=MYESTAPR1990
 NOTE: Libref MYFILES was successfully assigned as follows: 
       Engine:        V9 
       Physical Name: H:\Paper 3 SAS\SASFiles
 
 NOTE: The export data set has 1206 observations and 1212 variables.
 NOTE: "C:\Users\em9058\ESTMYESTAPR1990.xlsx" file was successfully created.
 NOTE: PROCEDURE EXPORT used (Total process time):
       real time           0.14 seconds
       cpu time            0.10 seconds
       
 
 FILE=MYESTMAY1990
 NOTE: Libref MYFILES was successfully assigned as follows: 
       Engine:        V9 
       Physical Name: H:\Paper 3 SAS\SASFiles
 
 NOTE: The export data set has 1219 observations and 1225 variables.
 NOTE: "C:\Users\em9058\ESTMYESTMAY1990.xlsx" file was successfully created.
 NOTE: PROCEDURE EXPORT used (Total process time):
       real time           0.14 seconds
       cpu time            0.07 seconds
       

 
 FILE=MYESTJUN1990
 NOTE: Libref MYFILES was successfully assigned as follows: 
       Engine:        V9 
       Physical Name: H:\Paper 3 SAS\SASFiles
 
 NOTE: The export data set has 1224 observations and 1230 variables.
 NOTE: "C:\Users\em9058\ESTMYESTJUN1990.xlsx" file was successfully created.
 NOTE: PROCEDURE EXPORT used (Total process time):
       real time           0.13 seconds
       cpu time            0.06 seconds
       
 
 FILE=MYESTJUL1990
 NOTE: Libref MYFILES was successfully assigned as follows: 
       Engine:        V9 
       Physical Name: H:\Paper 3 SAS\SASFiles
 
 NOTE: The export data set has 1227 observations and 1233 variables.
 NOTE: "C:\Users\em9058\ESTMYESTJUL1990.xlsx" file was successfully created.
 NOTE: PROCEDURE EXPORT used (Total process time):
       real time           0.15 seconds
       cpu time            0.10 seconds
       

 
 FILE=MYESTAUG1990
 NOTE: Libref MYFILES was successfully assigned as follows: 
       Engine:        V9 
       Physical Name: H:\Paper 3 SAS\SASFiles
 
 NOTE: The export data set has 1243 observations and 1249 variables.
 NOTE: "C:\Users\em9058\ESTMYESTAUG1990.xlsx" file was successfully created.
 NOTE: PROCEDURE EXPORT used (Total process time):
       real time           0.13 seconds
       cpu time            0.10 seconds
       
 
 FILE=MYESTSEP1990
 NOTE: Libref MYFILES was successfully assigned as follows: 
       Engine:        V9 
       Physical Name: H:\Paper 3 SAS\SASFiles
 
 NOTE: The export data set has 1251 observations and 1257 variables.
 NOTE: "C:\Users\em9058\ESTMYESTSEP1990.xlsx" file was successfully created.
 NOTE: PROCEDURE EXPORT used (Total process time):
       real time           0.14 seconds
       cpu time            0.06 seconds
       

 
 FILE=MYESTOCT1990
 NOTE: Libref MYFILES was successfully assigned as follows: 
       Engine:        V9 
       Physical Name: H:\Paper 3 SAS\SASFiles
 
 NOTE: The export data set has 1251 observations and 1257 variables.
 NOTE: "C:\Users\em9058\ESTMYESTOCT1990.xlsx" file was successfully created.
 NOTE: PROCEDURE EXPORT used (Total process time):
       real time           0.15 seconds
       cpu time            0.12 seconds
       
 
 FILE=MYESTNOV1990
 NOTE: Libref MYFILES was successfully assigned as follows: 
       Engine:        V9 
       Physical Name: H:\Paper 3 SAS\SASFiles
 
 NOTE: The export data set has 1265 observations and 1271 variables.
 NOTE: "C:\Users\em9058\ESTMYESTNOV1990.xlsx" file was successfully created.
 NOTE: PROCEDURE EXPORT used (Total process time):
       real time           0.14 seconds
       cpu time            0.07 seconds
       

 
 FILE=MYESTDEC1990
 NOTE: Libref MYFILES was successfully assigned as follows: 
       Engine:        V9 
       Physical Name: H:\Paper 3 SAS\SASFiles
 
 NOTE: The export data set has 1266 observations and 1272 variables.
 NOTE: "C:\Users\em9058\ESTMYESTDEC1990.xlsx" file was successfully created.
 NOTE: PROCEDURE EXPORT used (Total process time):
       real time           0.15 seconds
       cpu time            0.12 seconds
       
 
 FILE=MYESTJAN1991
 NOTE: Libref MYFILES was successfully assigned as follows: 
       Engine:        V9 
       Physical Name: H:\Paper 3 SAS\SASFiles
 
 NOTE: The export data set has 1292 observations and 1298 variables.
 NOTE: "C:\Users\em9058\ESTMYESTJAN1991.xlsx" file was successfully created.
 NOTE: PROCEDURE EXPORT used (Total process time):
       real time           0.15 seconds
       cpu time            0.10 seconds
       

 
 FILE=MYESTFEB1991
 NOTE: Libref MYFILES was successfully assigned as follows: 
       Engine:        V9 
       Physical Name: H:\Paper 3 SAS\SASFiles
 
 NOTE: The export data set has 1300 observations and 1306 variables.
 NOTE: "C:\Users\em9058\ESTMYESTFEB1991.xlsx" file was successfully created.
 NOTE: PROCEDURE EXPORT used (Total process time):
       real time           0.15 seconds
       cpu time            0.09 seconds
       
 
 FILE=MYESTMAR1991
 NOTE: Libref MYFILES was successfully assigned as follows: 
       Engine:        V9 
       Physical Name: H:\Paper 3 SAS\SASFiles
 
 NOTE: The export data set has 1300 observations and 1306 variables.
 NOTE: "C:\Users\em9058\ESTMYESTMAR1991.xlsx" file was successfully created.
 NOTE: PROCEDURE EXPORT used (Total process time):
       real time           0.14 seconds
       cpu time            0.10 seconds
       

 
 FILE=MYESTAPR1991
 NOTE: Libref MYFILES was successfully assigned as follows: 
       Engine:        V9 
       Physical Name: H:\Paper 3 SAS\SASFiles
 
 NOTE: The export data set has 1310 observations and 1316 variables.
 NOTE: "C:\Users\em9058\ESTMYESTAPR1991.xlsx" file was successfully created.
 NOTE: PROCEDURE EXPORT used (Total process time):
       real time           0.15 seconds
       cpu time            0.10 seconds
       
 
 FILE=MYESTMAY1991
 NOTE: Libref MYFILES was successfully assigned as follows: 
       Engine:        V9 
       Physical Name: H:\Paper 3 SAS\SASFiles
 
 NOTE: The export data set has 1328 observations and 1334 variables.
 NOTE: "C:\Users\em9058\ESTMYESTMAY1991.xlsx" file was successfully created.
 NOTE: PROCEDURE EXPORT used (Total process time):
       real time           0.17 seconds
       cpu time            0.10 seconds
       

 
 FILE=MYESTJUN1991
 NOTE: Libref MYFILES was successfully assigned as follows: 
       Engine:        V9 
       Physical Name: H:\Paper 3 SAS\SASFiles
 
 NOTE: The export data set has 1365 observations and 1371 variables.
 NOTE: "C:\Users\em9058\ESTMYESTJUN1991.xlsx" file was successfully created.
 NOTE: PROCEDURE EXPORT used (Total process time):
       real time           0.17 seconds
       cpu time            0.10 seconds
       
 
 FILE=MYESTJUL1991
 NOTE: Libref MYFILES was successfully assigned as follows: 
       Engine:        V9 
       Physical Name: H:\Paper 3 SAS\SASFiles
 
 NOTE: The export data set has 1372 observations and 1378 variables.
 NOTE: "C:\Users\em9058\ESTMYESTJUL1991.xlsx" file was successfully created.
 NOTE: PROCEDURE EXPORT used (Total process time):
       real time           0.17 seconds
       cpu time            0.12 seconds
       

 
 FILE=MYESTAUG1991
 NOTE: Libref MYFILES was successfully assigned as follows: 
       Engine:        V9 
       Physical Name: H:\Paper 3 SAS\SASFiles
 
 NOTE: The export data set has 1385 observations and 1391 variables.
 NOTE: "C:\Users\em9058\ESTMYESTAUG1991.xlsx" file was successfully created.
 NOTE: PROCEDURE EXPORT used (Total process time):
       real time           0.16 seconds
       cpu time            0.09 seconds
       
 
 FILE=MYESTSEP1991
 NOTE: Libref MYFILES was successfully assigned as follows: 
       Engine:        V9 
       Physical Name: H:\Paper 3 SAS\SASFiles
 
 NOTE: The export data set has 1398 observations and 1404 variables.
 NOTE: "C:\Users\em9058\ESTMYESTSEP1991.xlsx" file was successfully created.
 NOTE: PROCEDURE EXPORT used (Total process time):
       real time           0.18 seconds
       cpu time            0.12 seconds
       

 
 FILE=MYESTOCT1991
 NOTE: Libref MYFILES was successfully assigned as follows: 
       Engine:        V9 
       Physical Name: H:\Paper 3 SAS\SASFiles
 
 NOTE: The export data set has 1396 observations and 1402 variables.
 NOTE: "C:\Users\em9058\ESTMYESTOCT1991.xlsx" file was successfully created.
 NOTE: PROCEDURE EXPORT used (Total process time):
       real time           0.18 seconds
       cpu time            0.09 seconds
       
 
 FILE=MYESTNOV1991
 NOTE: Libref MYFILES was successfully assigned as follows: 
       Engine:        V9 
       Physical Name: H:\Paper 3 SAS\SASFiles
 
 NOTE: The export data set has 1408 observations and 1414 variables.
 NOTE: "C:\Users\em9058\ESTMYESTNOV1991.xlsx" file was successfully created.
 NOTE: PROCEDURE EXPORT used (Total process time):
       real time           0.19 seconds
       cpu time            0.07 seconds
       

 
 FILE=MYESTDEC1991
 NOTE: Libref MYFILES was successfully assigned as follows: 
       Engine:        V9 
       Physical Name: H:\Paper 3 SAS\SASFiles
 
 NOTE: The export data set has 1414 observations and 1420 variables.
 NOTE: "C:\Users\em9058\ESTMYESTDEC1991.xlsx" file was successfully created.
 NOTE: PROCEDURE EXPORT used (Total process time):
       real time           0.17 seconds
       cpu time            0.12 seconds
       
 
 FILE=MYESTJAN1992
 NOTE: Libref MYFILES was successfully assigned as follows: 
       Engine:        V9 
       Physical Name: H:\Paper 3 SAS\SASFiles
 
 NOTE: The export data set has 1415 observations and 1421 variables.
 NOTE: "C:\Users\em9058\ESTMYESTJAN1992.xlsx" file was successfully created.
 NOTE: PROCEDURE EXPORT used (Total process time):
       real time           0.17 seconds
       cpu time            0.12 seconds
       

 
 FILE=MYESTFEB1992
 NOTE: Libref MYFILES was successfully assigned as follows: 
       Engine:        V9 
       Physical Name: H:\Paper 3 SAS\SASFiles
 
 NOTE: The export data set has 1431 observations and 1437 variables.
 NOTE: "C:\Users\em9058\ESTMYESTFEB1992.xlsx" file was successfully created.
 NOTE: PROCEDURE EXPORT used (Total process time):
       real time           0.17 seconds
       cpu time            0.09 seconds
       
 
 FILE=MYESTMAR1992
 NOTE: Libref MYFILES was successfully assigned as follows: 
       Engine:        V9 
       Physical Name: H:\Paper 3 SAS\SASFiles
 
 NOTE: The export data set has 1443 observations and 1449 variables.
 NOTE: "C:\Users\em9058\ESTMYESTMAR1992.xlsx" file was successfully created.
 NOTE: PROCEDURE EXPORT used (Total process time):
       real time           0.19 seconds
       cpu time            0.17 seconds
       

 
 FILE=MYESTAPR1992
 NOTE: Libref MYFILES was successfully assigned as follows: 
       Engine:        V9 
       Physical Name: H:\Paper 3 SAS\SASFiles
 
 NOTE: The export data set has 1443 observations and 1449 variables.
 NOTE: "C:\Users\em9058\ESTMYESTAPR1992.xlsx" file was successfully created.
 NOTE: PROCEDURE EXPORT used (Total process time):
       real time           0.23 seconds
       cpu time            0.11 seconds
       
 
 FILE=MYESTMAY1992
 NOTE: Libref MYFILES was successfully assigned as follows: 
       Engine:        V9 
       Physical Name: H:\Paper 3 SAS\SASFiles
 
 NOTE: The export data set has 1463 observations and 1469 variables.
 NOTE: "C:\Users\em9058\ESTMYESTMAY1992.xlsx" file was successfully created.
 NOTE: PROCEDURE EXPORT used (Total process time):
       real time           0.19 seconds
       cpu time            0.10 seconds
       

 
 FILE=MYESTJUN1992
 NOTE: Libref MYFILES was successfully assigned as follows: 
       Engine:        V9 
       Physical Name: H:\Paper 3 SAS\SASFiles
 
 NOTE: The export data set has 1476 observations and 1482 variables.
 NOTE: "C:\Users\em9058\ESTMYESTJUN1992.xlsx" file was successfully created.
 NOTE: PROCEDURE EXPORT used (Total process time):
       real time           0.20 seconds
       cpu time            0.11 seconds
       
 
 FILE=MYESTJUL1992
 NOTE: Libref MYFILES was successfully assigned as follows: 
       Engine:        V9 
       Physical Name: H:\Paper 3 SAS\SASFiles
 
 NOTE: The export data set has 1482 observations and 1488 variables.
 NOTE: "C:\Users\em9058\ESTMYESTJUL1992.xlsx" file was successfully created.
 NOTE: PROCEDURE EXPORT used (Total process time):
       real time           0.20 seconds
       cpu time            0.15 seconds
       

 
 FILE=MYESTAUG1992
 NOTE: Libref MYFILES was successfully assigned as follows: 
       Engine:        V9 
       Physical Name: H:\Paper 3 SAS\SASFiles
 
 NOTE: The export data set has 1490 observations and 1496 variables.
 NOTE: "C:\Users\em9058\ESTMYESTAUG1992.xlsx" file was successfully created.
 NOTE: PROCEDURE EXPORT used (Total process time):
       real time           0.21 seconds
       cpu time            0.14 seconds
       
 
 FILE=MYESTSEP1992
 NOTE: Libref MYFILES was successfully assigned as follows: 
       Engine:        V9 
       Physical Name: H:\Paper 3 SAS\SASFiles
 
 NOTE: The export data set has 1502 observations and 1508 variables.
 NOTE: "C:\Users\em9058\ESTMYESTSEP1992.xlsx" file was successfully created.
 NOTE: PROCEDURE EXPORT used (Total process time):
       real time           0.19 seconds
       cpu time            0.14 seconds
       

 
 FILE=MYESTOCT1992
 NOTE: Libref MYFILES was successfully assigned as follows: 
       Engine:        V9 
       Physical Name: H:\Paper 3 SAS\SASFiles
 
 NOTE: The export data set has 1545 observations and 1551 variables.
 NOTE: "C:\Users\em9058\ESTMYESTOCT1992.xlsx" file was successfully created.
 NOTE: PROCEDURE EXPORT used (Total process time):
       real time           0.22 seconds
       cpu time            0.12 seconds
       
 
 FILE=MYESTNOV1992
 NOTE: Libref MYFILES was successfully assigned as follows: 
       Engine:        V9 
       Physical Name: H:\Paper 3 SAS\SASFiles
 
 NOTE: The export data set has 1563 observations and 1569 variables.
 NOTE: "C:\Users\em9058\ESTMYESTNOV1992.xlsx" file was successfully created.
 NOTE: PROCEDURE EXPORT used (Total process time):
       real time           0.20 seconds
       cpu time            0.20 seconds
       

 
 FILE=MYESTDEC1992
 NOTE: Libref MYFILES was successfully assigned as follows: 
       Engine:        V9 
       Physical Name: H:\Paper 3 SAS\SASFiles
 
 NOTE: The export data set has 1577 observations and 1583 variables.
 NOTE: "C:\Users\em9058\ESTMYESTDEC1992.xlsx" file was successfully created.
 NOTE: PROCEDURE EXPORT used (Total process time):
       real time           0.25 seconds
       cpu time            0.15 seconds
       
 
 101        
 102        OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 115

Re: Export multiple SAS data files in Excel Macro

Reeza — Thu, 16 Apr 2020 02:05:14 GMT

You modify the outfile portion of the code.

outfile=estmyest&file

It looks like you haven't specified any path so you can add that on and it should work..I'd try it with a few just in case.

outfile="C:\mycustomPath\estmyest&file"

Re: Export multiple SAS data files in Excel Macro

Shmuel — Thu, 16 Apr 2020 03:32:09 GMT

Looking at your log I found those two lines:

91         %let _DATAOUT_NAME=myest&file.xlsx;
 92         
 93         /* %let _DATAOUT_NAME='H:/Paper 3 SAS/SASFiles/Estimates/myest&file.csv';  */

Replace the single quotes into double quotes :

91        /*  %let _DATAOUT_NAME=myest&file.xlsx; */
 92         
 93           %let _DATAOUT_NAME = "H:/Paper 3 SAS/SASFiles/Estimates/myest&file.csv";

Re: Export multiple SAS data files in Excel Macro

saraphdnz — Thu, 16 Apr 2020 11:41:21 GMT

Hello Reeza,

Thanks for pointing out the issue, much appreciated for your help.

Regards, Sara

topic Re: Export multiple SAS data files in Excel Macro in SAS Procedures

Export multiple SAS data files in Excel Macro

Re: Export multiple SAS data files in Excel Macro

Re: Export multiple SAS data files in Excel Macro

Re: Export multiple SAS data files in Excel Macro