Allocation of library - %lib(library name)

[piddy]

Hi everybody

I hope you can help me with a question. At my work I have made a macro to allocate all of our data library's with the macro, %lib(name of library ). See below for just one of these libraries:

In my current solution based upon the code below I allocate the library with writing: %lib(ADHOC).

After macro has been put in production there has been a wish in the department to be able to write ADHOC as either adhoc, Adhoc, ADHOC or how ever you can write it.

I know of the upcase, lowcase, propcase function but I am not sure how to implemet it in my code below.

I hope you can help me with this and many thanks in advance. 

Kind regards

Thomas

%macro lib(lib);

%let program=\\ipt202wp.iplocal.dk\f-drev; %put &program;

%let AdHoc = '&program\adhoc';

%if "&lib" = "ADHOC"

%then libname adhoc "&program.\adhoc";

%else

%put *************** REFERENCE &lib IS UNKNOWN - TRY AGAIN 🙂 ***************;

%mend;

[Shmuel]

What OS do you use - Windows or Unix/Linux or other ?

As mutch as I know, Windows is not sensitive to case of letters, but Unix and Linux are sensitive

and you must replace the user's spellinhg to the right spelling as it is written on the HD.

[piddy]

It's a Windows server running SAS.

[Shmuel]

On windows server you may leave the spelling as is, but from the estatic point of view and

for educational point of view I suggest do either UPCASE or lowcase - according to your prefference.

[Shmuel]

few more points:

when using macro variable there is no need to enter the string between apostrophs:

    %let  lib = ABCD    is right

    %let lib='ABCD'      may cause truble

     libname  &lib  "&program/&lib";     may work fine with double quotes

     libname  &lib  '&program/&lib';      will not work

[ballardw]

What is the intent of this:

%if "&lib" = "ADHOC"?

If you are checking to see if the value passed to the macro is ADHOC then quotes are not needed.

But you may want to see about the case. The macro may have issues if calle as %lib(Adhoc); or %lib(aDhoc); etc.

%if upcase(&lib) = ADHOC %then %do ;

[piddy]

I the top of a program where I allocate a libery, normally you could would write this for example:

libname wfstat '\\ipt202wp.iplocal.dk\f-drev\wfstat';

 With the macro in my post I can just write:

%lib(WFSTAT);

My question is how can I adjust my code so I both can write

%lib(WFSTAT);

%lib(wfstat);

%lib(Wfstat);

or how ever it can be spelled.

[Tom]

You probably want a macro like this.  Personally since I normally use Unix systems I would force the actual directory name to lowercase.  If you are using Windows servers then that might not matter to you could eliminate the code to convert the value provided to lowercase.  Also instead of checking if the file (directory) exists you could just try to generate the libname and SAS will throw the error message for you. Or you could check the automatic macro variable SYSLIBRC to see if the libname statement worked.

%macro lib(lib);
%local path ;
%let path=\\ipt202wp.iplocal.dk\f-drev\%sysfunc(lowcase(&lib)) ;

%if %sysfunc(fileexist(&path)) %then %do;
  libname &lib "&path" ;
%end;
%else %do ;
  %put ERROR: &=lib is invalid.  Could not find &=path ;
%end;
%mend lib ;
 
%lib(WFSTAT);

[Shmuel · Accepted solution]

Here is your code addapted to your query:

%macro lib(lib);

     %let program=\\ipt202wp.iplocal.dk\f-drev\;

     %let lib = %sysfunc(lowcase(&lib));

     %do; libname &lib  "&program.\&lib";

  %mend;

I have droped the %if statement, but if you want to check validity of values then change to:

%macro lib(lib);

     %let program=\\ipt202wp.iplocal.dk\f-drev\;

     %let lib = %sysfunc(lowcase(&lib));

    %if &lib = wfstat %or &lib = adhoc %or ....   %then

          %do; libname &lib  "&program.\&lib";

    %else %put   ... /* your message for invalid refference */

  %mend;

[piddy]

Thanks. So however the user writes the name of the libery(WFSTAT,wfstat,Wfstat etc.) your solution converts all input in the %lib() to lowcase so there will be no hickups when running the code.

Is that understood correcly?

[Shmuel]

Correct.

[Shmuel]

OOPS, sorry;

I have missed the %end for the %do

[piddy]

Hi again

In my current macro I also have incorporated to use %lib() to access SQL databases. See below. I tried to make it work with the macro new macro you provided but I couldn't figure it out.

The macro below works fine except the thing about I want the user to be able to write the libery name in the () as they want. So either lowcase, upcase etc.  As the macro is now it only excepts UPCASE because that's how I have written them in the code.

%macro lib(lib);

%let program=\\ipt202wp.iplocal.dk\f-drev; %put &program;

%let AdHoc = '&program\adhoc';

%let afstindb = '&program\Afstemning\Afst_indbetal';

%let analyse = '&program\analysedata';

/***************************************************************

**************************ALLOCATION OF LIBERY'S*****

****************************************************************/

/**Local data libery's**/

%if "&lib" = "ADHOC"

%then libname adhoc "&program.\adhoc";

%else

%if "&lib" = "AFSTINDB"

%then libname afstindb "&program.\Afstemning\Afst_indbetal";

%else

%if "&lib" = "ANALYSE"

%then libname analyse "&program.\analysedata";

%else

/************ALLOCATION OF ALL LIBERYS IN ONE CALL***********/

%if "&lib" = "AKTUARY"

%then %do;

%lib(ADHOC);

%lib(AFSTINDB);

%lib(ANALYSE);

%end;

%else

/****************ALLOCATION OF SQL LIBERY'S******************/

%if "&lib." = "WFSAP"

%then libname WFSAP ODBC

complete ="DRIVER={SQL server};

server=SERVERPATH\NAME;

Database=DATABASENAME;

Trusted_Connection=yes;";

%else

%if "&lib." = "ISS"

%then libname ISS ODBC

complete ="DRIVER={SQL server};

Server=SERVERPATH\NAME;

Database=DATABASENAME;

Trusted_Connection=yes;

User id=;

Password=; ";

/************ALLOCATION OF ALL SQL-LIBERYS IN ONE CALL************/

%if "&lib" = "SQL"

%then %do;

%lib(WFSAP);

%lib(ISS);

%end;

%else

%put *************** REFERENCE &lib IS UNKNOWN - TRY AGAIN ***************;

%mend;

[Shmuel]

You need to add just one line after the %macro line:

      %let lib = %upcase(&lib);

Then the user can write it as he want.

Thae above line converts it to upcase for your %IF statements.