topic Re: Proc Import XLSX with Libname Instead of Full Filename in SAS Programming

Proc Import XLSX with Libname Instead of Full Filename

bttomblin — Fri, 28 Jul 2023 16:48:10 GMT

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!

Re: Proc Import XLSX with Libname Instead of Full Filename

ErikLund_Jensen — Fri, 28 Jul 2023 17:16:53 GMT

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;

Re: Proc Import XLSX with Libname Instead of Full Filename

Reeza — Fri, 28 Jul 2023 17:37:41 GMT

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-Appendix/ta-p/291716

@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!

Re: Proc Import XLSX with Libname Instead of Full Filename

bttomblin — Fri, 28 Jul 2023 18:06:37 GMT

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.

Re: Proc Import XLSX with Libname Instead of Full Filename

bttomblin — Fri, 28 Jul 2023 18:08:16 GMT

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!

Re: Proc Import XLSX with Libname Instead of Full Filename

Tom — Fri, 28 Jul 2023 18:18:13 GMT

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

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

Re: Proc Import XLSX with Libname Instead of Full Filename

ballardw — Fri, 28 Jul 2023 20:06:03 GMT

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.