I am trying to import multiple EXCEL files that are loaded into a shared directory on SAS server. I am using EG 7.13 HF6 (32-BIT)
The dataset "files" created and holds all correct excel file names, all the SYMBOLGEN in log file looks correct and the MCARO compiled ok. However, when I called it, the datafile has one extra slash created in the path.
I tried many approached from previous posts and still not working.
Please help.
Thanks
filename dirlist pipe 'dir "/shared/MCL_Team/MCL_1117/"';
data files (drop=dotpos len);
length fname $150 outname $150;
infile dirlist truncover length= reclen;
input fname $varying150. reclen;
fname =strip(fname);
fname =tranwrd(fname, "\", " ");
fname =tranwrd(fname, " ", " ");
outname = tranwrd(outname, "-", "_");
outname = tranwrd(outname, "&", "_");
outname = compress(outname);
put fname;
put outname;
dotpos =find(outname,'.', 1);
outname = substr(outname, 1, dotpos-1);
len = length(outname);
if len > 32 then outname = substr(outname, 1, 32);
run;
data _null_;
set files nobs= nobs;
call symput('nfile', nobs);
stop;
run;
%macro fileinput;
%local i;
%do i= 1 %to &nfile;
data _null_;
set files(firstobs= &i obs= &i);
call symput('fpath', "/SHARED/MCL_Team/MCL_1117/" || fname);
call symput('foutname', outname);
run;
proc import datafile="&fpath"
dbms=xlsx out=&foutname replace;
range="Sheet1$A1:H518";
sheet='Sheet1';
getnames=yes;
run;
%end;
%mend;
options symbolgen mprint;
%fileinput;
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
Just a guess, really ...
It's possible the functions you apply to get FNAME are in the wrong order. STRIP should be the last of the three. The reason: A leading "\" will become a leading blank. And STRIP should get rid of that leading blank, by being the last function applied.
**** EDITED:
And a second guess, just to rule out the possibility of this causing a problem: You can see there are extra blanks at the end of &FPATH. They appear on the log before the closing double-quote. Get rid of those extra trailing blanks with:
call symput('fpath', "/SHARED/MCL_Team/MCL_1117/" || trim(fname));
@AstoundingI tried both of your suggestions, and I still get the same error.
This is still an unsolved bug.
As a first step, did you confirm that if you just run the PROC import step (no macro code) it works?
proc import datafile="/SHARED/MCL_Team/MCL_1117/MCL-AAAA.xlsx" dbms=xlsx out=AAAA replace;
range="Sheet1$A1:H518";
sheet='Sheet1';
getnames=yes;
run;
Looks like you're on linux, so case sensitivity of file names/path names is always a potential problem.
Would also be interesting to see if you find an extra / in the log just submitting that code. If so, it might just be a PROC IMPORT oddity. PROC IMPORT sometimes shows odd statements in the log, like that:
MPRINT(FILEINPUT): RXLX;
you see in the log before the RANGE statement. So it's possible it's adding the slash.
In general, my understanding is linux will ignore a // in the middle of a path, and treat it the same as /. So the extra slash in the log could be a "red herring".
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 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.