BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
bttomblin
Calcite | Level 5

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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!


 

View solution in original post

6 REPLIES 6
ErikLund_Jensen
Rhodochrosite | Level 12

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
Calcite | Level 5

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
Super User

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
Calcite | Level 5

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
Super User Tom
Super User

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
Super User

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.

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 6 replies
  • 1859 views
  • 0 likes
  • 5 in conversation