Extra '/' inserted in datafile directory when calling MACRO to import multiple excel files

[vince_ott]

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;

[error_prone]

Posting log with symbolgen, mprint and mlogic active could be useful to debug the macro. We can't see what's in the dataset or variables.

[vince_ott]

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

[Astounding]

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));

[vince_ott]

@AstoundingI tried both of your suggestions, and I still get the same error. 

[mvrieze]

This is still an unsolved bug.

[Quentin]

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".