Hi @Shmuel,
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
