topic Re: extract libnames from excel and create macro variable to call libnames in SAS Programming

extract libnames from excel and create macro variable to call libnames

vraj1 — Mon, 23 Jan 2023 11:27:36 GMT

I have an excel file which contains libnames and i need to extract libnames from excel and create macro variable to call libnames.

I use proc import to import the libnames and then use the below code but since my libname has ":" it gives error

! C:\Programming\check

ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, (, ;, CUROBS, END, INDSNAME, KEY, KEYRESET,
KEYS, NOBS, OPEN, POINT, _DATA_, _LAST_, _NULL_.

/* Concatenate the list of LIBNAMEs into a single macro variable */
data _null_;
    set libname_list;
    if _n_ = 1 then call symputx('libnames', Href);
    else call symputx('libnames', symget('libnames') || ' ' || Href);
run;
/* Create a macro variable to call the assigned LIBNAMEs */

%let libnames_list = &libnames;

/* Perform some action on the assigned LIBNAMEs */
data _null_;
    set &libnames_list;
  
run;

Can anyone help on how to create macro which can be called on each program to get libnames

Re: extract libnames from excel and create macro variable to call libnames

andreas_lds — Mon, 23 Jan 2023 11:37:42 GMT

Please post the complete log using "insert code" and show the contents of "libname_list", usable form preferred.

Re: extract libnames from excel and create macro variable to call libnames

vraj1 — Mon, 23 Jan 2023 11:48:02 GMT

Excel with libname looks like this
          Href
_01 "C:\Programming\check1"
_02  "C:\Programming\check2"
_03 "C:\Programming\check3"

and then after proc import i use the below code to create macro variable so that i can call it in each individual program


/* Concatenate the list of LIBNAMEs into a single macro variable */
data _null_;
    set libname_list;
    if _n_ = 1 then call symputx('libnames', Href);
    else call symputx('libnames', symget('libnames') || ' ' || Href);
run;

/* Create a macro variable to call the assigned LIBNAMEs */
%let libnames_list = &libnames;

/* Perform some action on the assigned LIBNAMEs */
data _null_;
    set &libnames_list;
run;

I get the below error in log

      data _null_;
48             set &libnames_list;
ERROR: File WORK._01.DATA does not exist.
ERROR: File C:\Programming\check1 does not exist.
ERROR: File WORK._02.DATA does not exist.
ERROR: File C:\Programming\check2 does not exist.
ERROR: File WORK._03.DATA does not exist.
ERROR: File C:\Programming\check3 does not exist.

Re: extract libnames from excel and create macro variable to call libnames

Patrick — Mon, 23 Jan 2023 12:18:29 GMT

Please attach a sample Excel that is fully representative of your actual data (just a few lines) - even the ones not downloading Excels can preview the content to understand what you're really dealing with.

To use a SAS table in a data step it must either be accessed via <libref>.<table name> or then fully qualified as <path>/<file name>.

Assuming check2 is a SAS table for your data _null_ step generated syntax would need to look either...

libname aa 'c:\Programming';
data _null_;
   set aa.check2;
run;

...or...

data _null_;
  set 'c:\Programming\check2.sas7bdat' ;
run;

And last but not least: Your data step for creating the macro variable with concatenated values works but usually that's done using code as follows:

%let libnames=;
proc sql noprint;
  select href into :libnames separated by ' '
  from libname_list
  ;
quit;

Re: extract libnames from excel and create macro variable to call libnames

PaigeMiller — Mon, 23 Jan 2023 12:24:48 GMT

Please look at the SAS data set named libname_list and determine if it has been created correctly from the Excel file. Please show us (a portion of) the SAS data set named libname_list (NOT the Excel file) as working data step code, which you can type yourself or follow these instructions.

Re: extract libnames from excel and create macro variable to call libnames

Kurt_Bremser — Mon, 23 Jan 2023 12:44:13 GMT

It looks like your dataset imported from Excel contains logical names and physical path names, to be used in LIBNAME statements.

To execute a series of LIBNAME statements, do this:

data _null_;
set have; /* or whatever your datase is named */
call execute('libname ` !! href !! ' "' !! strip(path) !!'";');
run;

Assuming the second variable in your dataset is named PATH.

Re: extract libnames from excel and create macro variable to call libnames

vraj1 — Mon, 23 Jan 2023 13:09:11 GMT

Thanks Kurt, It works. I have one more question. How do i make it as a macro so that i can use on seperate programs?

Re: extract libnames from excel and create macro variable to call libnames

ballardw — Mon, 23 Jan 2023 15:37:44 GMT

@vraj1 wrote:

Thanks Kurt, It works. I have one more question. How do i make it as a macro so that i can use on seperate programs?

Do mean different SAS sessions?

Once a libname is defined in a SAS session it persists unless you explicitly clear it. So run this bit once and all your other programs have the libraries available.

Or if other users, which would mean different sessions, you could use %include to point to a common location source code and have them execute as needed but that would assume that all the files/ paths are the same.