topic Re: Extra '/' inserted in datafile directory when calling MACRO to import multiple excel files in SAS Programming

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

vince_ott — Thu, 11 Jan 2018 16:46:31 GMT

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;

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

error_prone — Thu, 11 Jan 2018 16:56:58 GMT

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.

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

Astounding — Thu, 11 Jan 2018 19:18:09 GMT

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

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

vince_ott — Thu, 11 Jan 2018 19:09:12 GMT

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

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

25 GOPTIONS ACCESSIBLE;

26 filename dirlist pipe 'dir "/shared/MCL_Team/MCL_1117/"' /*/b'*/;

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;

47 dotpos =find(outname,'.', 1);

48 outname = substr(outname, 1, dotpos-1);

49 len = length(outname);

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

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

60 %macro fileinput;

61 %local i;

62 %do i= 1 %to &nfile;

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;

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

80 %fileinput;

MPRINT(FILEINPUT): data _null_;

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

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

vince_ott — Thu, 11 Jan 2018 19:29:51 GMT

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

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

mvrieze — Mon, 15 Jun 2020 20:58:28 GMT

This is still an unsolved bug.

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

Quentin — Tue, 16 Jun 2020 14:46:23 GMT

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