Proc Import XLSX with Libname Instead of Full Filename

[bttomblin]

As a newer SAS user, I have learned the "proc import" method of importing .xlsx files using the full filename. Now that I am working with more files, I wanted to see if it was possible to use a defined libname or path when importing instead of the full filename. That way, if I have to move files to a new folder, I only have to change the path in one location instead of many.

Basically, I currently use this:
Proc import out= want
datafile="C:\Users\Example1\Example2\want.xlsx"
dbms=xlsx replace;
run;

And I want to know if there's any way to do something like:

libname Main "C:\Users\Example1\Example2";

Proc import out= want
datafile=Main.want.xlsx
dbms=xlsx replace;
run;

This is my first post on these forums, so let me know if I need more information. Thank you for any help!

[ErikLund_Jensen]

Hi @bttomblin 

Proc import and XLSX libname are two different things. With Proc Import, you specify the XLSX file and (optional) the sheet to import, if there are more than one.

With XLSX libname, you assign the XLSX file (not the folder) as a libname, and the sheets are seen as members in the libname. The correct syntax is:

libname main XLSX "C:\Users\Example1\Example2\want.xlsx";

* Copy all sheets to work; 
proc copy inlib=main outlib=work;
run;

[bttomblin]

I did see this as an option, but I think it would have the same issue of needing to update every xlsx library if files moved. Thank you for the response, though.

[Reeza · Accepted solution]

Macro variables?

%let file_path = C:\Users\Example1\Example2;

Proc import out= want
datafile="&file_path.\Main.want.xlsx"
dbms=xlsx replace;
run;

UCLA introductory tutorial on macro variables and macros

https://stats.idre.ucla.edu/sas/seminars/sas-macros-introduction/


Examples of common macro usage

https://communities.sas.com/t5/SAS-Communities-Library/SAS-9-4-Macro-Language-Reference-Has-a-New-Ap...

---

@bttomblin wrote:

As a newer SAS user, I have learned the "proc import" method of importing .xlsx files using the full filename. Now that I am working with more files, I wanted to see if it was possible to use a defined libname or path when importing instead of the full filename. That way, if I have to move files to a new folder, I only have to change the path in one location instead of many.

Basically, I currently use this:
Proc import out= want
datafile="C:\Users\Example1\Example2\want.xlsx"
dbms=xlsx replace;
run;

And I want to know if there's any way to do something like:

libname Main "C:\Users\Example1\Example2";

Proc import out= want
datafile=Main.want.xlsx
dbms=xlsx replace;
run;

This is my first post on these forums, so let me know if I need more information. Thank you for any help!

---

[bttomblin]

Macros were going to be my fallback if what I proposed didn't exist. I agree with you that I think that is the closest to a one-place-change variable to edit should my file paths change. Thanks!

[Tom]

You could get the path from the libref (or fileref) instead of using a macro variable.

proc import file="%sysfunc(pathname(MYLIB))/myfile.xlsx" ...

[ballardw]

Just for consideration have you compared the results of importing two or more XLSX files that are supposed to contain similar data? Compare meaning look at

1) variable names

2) variable types

3) length of variables

Import makes guesses for each file. You will find literally dozens if not hundreds of topics on this forum related to importing multiple similar XLSX files and getting results where data gets truncated when combining sets because of defined lengths or cannot combine files at all because the type for the same named variables are different.

Or can't use with other external programs as needed because account numbers and such have had leading 0, zero, removed when a value is treated as numeric.

Two different installs running Proc Import may even get different results based on custom settings.