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
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"
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"
Hello Reeza,
Thanks for pointing out the issue, much appreciated for your help.
Regards, Sara
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";
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: