How to dynamically import excel files in SAS?

Question

I'm a new SAS user and I have a question regarding importing excel files into SAS.

I have an excel file in my shared drive which is mapped differently on my PC's. (the path is labeled as S drive on one PC, and the same path is the labeled as Y drive on another PC). Whenever I change my PC and run my SAS program it usually fails to import it because I didn't remap the excel file.

Is there a way to import an excel file from this folder without having to remapped the path to the excel file every time I move PCs? Thanks for you help.

I don't know anything about SAS, but with regards to the path you should use a UNC instead. lifewire.com/unc-universal-naming-convention-818230 — Nick.McDermaid
Agreed. In cmd.exe type C:>net use to see to what server addresses your network drive letters map. — Hugs

Joe Joe · Accepted Answer · 2017-03-17T03:50:14

Certainly one option is to use a UNC as noted in a comment. A UNC is a path that looks similar to //share/folder/otherfolder/file.xlsx and will not depend on drive mappings.

However, if that's not feasible for some reason, you can certainly work around that. How you do so depends first on how you're running SAS.

If you're using Enterprise Guide to connect, for example, a prompt is probably your best bet. Prompts define macro variables, and macro variable can be used for the filename (or for the drive letter).

If you're using other methods, prompts of various forms may still be available, but you may find it easier to create a local file that defines the filename (or libname) or even just stores the drive letter in a macro variable.

If you have full control of SAS, and this is something that is generally useful (not just for this one program), you could put something in your autoexec.sas. If you don't have that level of control, or you want this specific to this program only, you could create a file that will be in the same place (for example, c:\SASFiles) on both PCs, and on each PC have a different filename/libname/macro variable (the one appropriate for that PC). Then you %include that file at the start of your program.

Finally, you may be able to use the PC name or other environment variables to determine which machine you're on, which you could then use to drive logic. For example:

%macro define_path;
  %global path_letter;
  %if &syshostname = 55PC1010G %then %do;
     %let path_letter=P;
  %end;
  %else %let path_letter=Y;
%mend define_path;
%define_path;
%put &=path_letter;

You'll want to %put &=syshostname on each machine to see what the values are, but it should be easy enough to figure out. If &syshostname isn't defined for some reason, %put _automatic_ will tell you what is, and one of the variables there should be useful enough.

How to dynamically import excel files in SAS?

1 Answers