This is the log file.. some information were replaced by letters to protect privacy. 1 The SAS System 09:39 Thursday, January 11, 2018 1 ;*';*";*/;quit;run; 2 OPTIONS PAGENO=MIN; 3 %LET _CLIENTTASKLABEL='Import_multiple_excel_incomplete'; 4 %LET _CLIENTPROCESSFLOWNAME='Process Flow'; 5 %LET _CLIENTPROJECTPATH=''; 6 %LET _CLIENTPROJECTPATHHOST=''; 7 %LET _CLIENTPROJECTNAME=''; 8 %LET _SASPROGRAMFILE='U:\SAS Project\Import_multiple_excel_incomplete.sas'; 9 %LET _SASPROGRAMFILEHOST='ABC101845'; 10 11 ODS _ALL_ CLOSE; 12 OPTIONS DEV=ACTIVEX; 13 GOPTIONS XPIXELS=0 YPIXELS=0; 14 FILENAME EGSR TEMP; 15 ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR 16 STYLE=HtmlBlue 17 STYLESHEET=(URL="file:///C:/Program%20Files/SASHome/SASEnterpriseGuide/7.1/Styles/HtmlBlue.css") 18 NOGTITLE 19 NOGFOOTNOTE 20 GPATH=&sasworklocation SYMBOLGEN: Macro variable SASWORKLOCATION resolves to "/saswork/xxxx/SAS_work8FE60000749D_sas-0001619/SAS_workE77A0000749D_sas-0001619/" 21 ENCODING=UTF8 22 options(rolap="on") 23 ; NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR 24 25 GOPTIONS ACCESSIBLE; 26 filename dirlist pipe 'dir "/shared/MCL_Team/MCL_1117/"' /*/b'*/; 27 28 data files (drop=dotpos len); 29 length fname $150 outname $150; 30 infile dirlist truncover length= reclen; 31 input fname $varying150. reclen; 32 fname =strip(fname); 33 fname =tranwrd(fname, "\", " "); 34 fname =tranwrd(fname, " ", " "); 35 outname = tranwrd(fname, " Clarification required", " "); 36 outname = tranwrd(outname, "MCL-", " "); 37 outname = tranwrd(outname, "MCL - ", " "); 38 outname = tranwrd(outname, "MCL ", ""); 39 outname = tranwrd(outname, " 2017-10", " "); 40 outname = tranwrd(outname, " 2017", " "); 41 outname = tranwrd(outname, "-", "_"); 42 outname = tranwrd(outname, "&", "_"); 43 outname = compress(outname); 44 put fname; 45 put outname; 46 47 dotpos =find(outname,'.', 1); 48 outname = substr(outname, 1, dotpos-1); 49 len = length(outname); 50 51 if len > 32 then outname = substr(outname, 1, 32); 52 run; 2 The SAS System 09:39 Thursday, January 11, 2018 NOTE: The infile DIRLIST is: Pipe command="dir "/shared/MCL_Team/MCL_1117/"" MCL-AAAA.xlsx AAAA.xlsx MCL-BB.xlsx BB.xlsx MCL-CCCCCC.xlsx CCCCCC.xlsx MCL DDDDD.xlsx DDDDD.xlsx . . . . NOTE: 24 records were read from the infile DIRLIST. The minimum record length was 27. The maximum record length was 68. NOTE: The data set WORK.FILES has 24 observations and 2 variables. NOTE: DATA statement used (Total process time): real time 0.04 seconds cpu time 0.06 seconds 3 The SAS System 09:39 Thursday, January 11, 2018 53 54 data _null_; 55 set files nobs= nobs; 56 call symput('nfile', nobs); 57 stop; 58 run; NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column). 56:24 NOTE: There were 1 observations read from the data set WORK.FILES. NOTE: DATA statement used (Total process time): real time 0.01 seconds cpu time 0.01 seconds 59 60 %macro fileinput; 61 %local i; 62 %do i= 1 %to &nfile; 63 64 data _null_; 65 set files(firstobs= &i obs= &i); 66 call symput('fpath', "/SHARED/MCL_Team/MCL_1117/" || fname); 67 call symput('foutname', outname); 68 run; 69 70 proc import datafile="&fpath" 71 dbms=xlsx out=&foutname replace; 72 range="Sheet1$A1:H518"; 73 sheet='Sheet1'; 74 getnames=yes; 75 run; 76 %end; 77 %mend; 78 options symbolgen mprint; SYMBOLGEN: Macro variable NFILE resolves to 24 79 80 %fileinput; MPRINT(FILEINPUT): data _null_; SYMBOLGEN: Macro variable I resolves to 1 SYMBOLGEN: Macro variable I resolves to 1 MPRINT(FILEINPUT): set files(firstobs= 1 obs= 1); MPRINT(FILEINPUT): call symput('fpath', "/SHARED/MCL_Team/MCL_1117/" || fname); MPRINT(FILEINPUT): call symput('foutname', outname); MPRINT(FILEINPUT): run; NOTE: There were 1 observations read from the data set WORK.FILES. NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.01 seconds SYMBOLGEN: Macro variable FPATH resolves to /SHARED/MCL_Team/MCL_1117/MCL-AAAA.xlsx SYMBOLGEN: Macro variable FOUTNAME resolves to AAAA 4 The SAS System 09:39 Thursday, January 11, 2018 MPRINT(FILEINPUT): proc import datafile="/SHARED/MCL_Team/MCL_1117/MCL-AAAA.xlsx " dbms=xlsx out=AAAA replace; MPRINT(FILEINPUT): RXLX; MPRINT(FILEINPUT): range="Sheet1$A1:H518"; MPRINT(FILEINPUT): sheet='Sheet1'; MPRINT(FILEINPUT): getnames=yes; MPRINT(FILEINPUT): run; ERROR: Physical file does not exist, /SHARED/MCL_Team/MCL_1117//MCL-AAAA.xlsx. NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE IMPORT used (Total process time): real time 0.00 seconds cpu time 0.01 seconds
... View more