coordination of libname statemen

[MMSTAT]

options mrecall;
%macro importXLSX(data,data2,getnames);
options validVarName=any;
proc import datafile="/home/u50158717/macros/&data..xlsx"
dbms=xlsx 
out=&data2
replace;
getnames=&getnames;
sheet="Sheet2";
*datarow=n;
*range="range name";
*mixed=yes;
run; quit;
%mend importXLSX;

libname ???
%importXLSX(Sheet2.xlsx,Sheet2.xlsx,getnames=yes);

[Patrick]

As long as you define the libref (libname statement) before you use it you're good to go. With your code this could look like:

options dlcreatedir;
libname target "/home/u50158717/results";
%importXLSX(myExcel,target.mySASTable,yes);

[MMSTAT]

libname target "/home/u63984272/macros";

Both the data and this macro are in "/home/u63984272/macros";

where should the libname go?

70 %macro importXLSX(data,data2,getnames);
71 options validVarName=any;
72 proc import datafile="/home/u63984272/macros/&data..xlsx"
73 dbms=xlsx
74 out=&data2
75 replace;
76 getnames=&getnames;
77 sheet="Sheet2";
78 *datarow=n;
79 *range="range name";
80 *mixed=yes;
81 run; quit;
82 %mend importXLSX;
83
84 options dlcreatedir;
85 libname target "/home/u63984272/macros";
NOTE: Libref TARGET was successfully assigned as follows:
Engine: V9
Physical Name: /home/u63984272/macros

86 %importXLSX(data=sheet2.XLSX,data2=sheet2.XLSX,getnames=yes);

ERROR: Library name is not assigned.
NOTE: The SAS System stopped processing this step because of errors.

[Patrick]

@MMSTAT You're passing wrong parameter values to your macro. Compare it with the macro call I've already posted. The 2nd parameter must be the target table name (<libref>.<table name>) and the 3rd parameter may only include the parameter value (yes/no).

[Kurt_Bremser]

You supply the parameter data2 as
sheet2.XLSX. This identifies the dataset as xlsx in the library sheet2.
If you want to create a dataset sheet2 in the library you defined, you must set
data2=target.sheet2.

[MMSTAT]

1          OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK; 68

• options mrecall;
• %macro importXLSX(data,data2,getnames);
• options validVarName=any;
• proc import datafile="/home/u63984272/macros/&data..xlsx"
• dbms=xlsx
• out=&data2
• replace;
• getnames=&getnames;
• sheet="Sheet2";
• *datarow=n;
• *range="range name";
• *mixed=yes;
• run; quit;
• %mend importXLSX; 83
• options dlcreatedir;
• libname target "/home/u63984272/macros"; NOTE: Libref TARGET was successfully assigned as follows:

Engine:        V9

Physical Name: /home/u63984272/macros

• %importXLSX(data=sheet2.XLSX,data2=target.sheet2,getnames=yes);

ERROR: Physical file does not exist, /home/u63984272/macros//sheet2.XLSX. NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE IMPORT used (Total process time):

folder target is empty and sheet2.xlsx is only in /home/u63984272/macros//sheet2.XLSX.

[ballardw]

Please always look very closely at the entire PATH and file names when SAS says something doesn't exist.

Your log shows:

ERROR: Physical file does not exist, /home/u63984272/macros//sheet2.XLSX. NOTE: The SAS System stopped processing this step because of errors.

Note the 2 slashes. Does that match your real file location?

Strong suggestion: Paste code into a text or code box opened by clicking on one of the </> or "running man" icons above the message window.

The main message windows on this forum will reformat pasted text and the bit where your "code" appears with bullet points on each line of the macro indicates that may have happened, possibly obscuring other characters that may be the cause of the error in the file path.

Similarly, copy text from the LOG and paste it into a text box to prevent the forum from changing the text that actually appeared in the log. The text boxes also help separate the code/log under discussion from the discussion text.

[Quentin]

I suggest you turn on system option MPRINT, and then review the SAS code that is generated by your macro.

With this macro code:

%macro importXLSX(data,data2,getnames);
  %put my Excel file is: "/home/u63984272/macros/&data..xlsx" ;
  options validVarName=any;
  proc import datafile="/home/u63984272/macros/&data..xlsx"
    dbms=xlsx
    out=&data2
    replace;
    getnames=&getnames;
    sheet="Sheet2";
  run; quit;
%mend importXLSX;

If you call the macro like:

%importXLSX(data=sheet2.XLSX,data2=target.sheet2,getnames=yes)

You will see the macro generates this PROC IMPORT statement:

proc import datafile="/home/u63984272/macros/sheet2.XLSX.xlsx" dbms=xlsx out=target.sheet2 replace;

Note it is looking for a file named sheet2.XLSX.xlsx.  This is likely incorrect. What is the name of the Excel file you are trying to import?

[Tom]

So you passed these arguments to the macro (notice how much clearer it is when you pass all of the arguments by name instead of just the last one):

%importXLSX(data=Sheet2.xlsx,data2=Sheet2.xlsx,getnames=yes);

So that will result in this SAS code being generated:

proc import datafile="/home/u50158717/macros/Sheet2.xlsx.xlsx"
dbms=xlsx 
out=Sheet2.xlsx
replace;
getnames=yes;
sheet="Sheet2";
run;

Do you really have a file named?

"/home/u50158717/macros/Sheet2.xlsx.xlsx"

Do you really want to make a SAS dataset named XLSX?  If so then into which directory do you want the PROC IMPORT step to write the xlsx.sas7bdat dataset that will be created ?  Once you figure that out then define the SHEET2 libref in the LIBNAME statement before the macro call.  Perhaps something like:

libname sheet2 "/home/u50158717/sheet2/";

[Tom]

Why bother to make a macro for such a simple task?

Point the libref to the folder where you want the SAS datasets to be written.

The first part of the dataset name is the libref and the second part the dataset names.

So something like this:

libname target "/home/u50158717/datasets/";
proc import
 datafile="/home/u50158717/macros/myfile.xlsx"
 dbms=xlsx replace
 out=target.myfile_sheet2
;
  sheet="Sheet2";
run;

 Note: PROC IMPORT does not support using quoted physical names for SAS datasets, even though that syntax works fine in DATA steps and other parts of SAS code.