BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
piddy
Fluorite | Level 6

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Shmuel
Garnet | Level 18

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;

 

View solution in original post

13 REPLIES 13
Shmuel
Garnet | Level 18

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
Fluorite | Level 6

It's a Windows server running SAS.

Shmuel
Garnet | Level 18

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
Garnet | Level 18

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

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
Fluorite | Level 6

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

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
Garnet | Level 18

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
Fluorite | Level 6
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
Garnet | Level 18

OOPS, sorry;

 

I have missed the %end for the %do

piddy
Fluorite | Level 6

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
Garnet | Level 18

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 13 replies
  • 1550 views
  • 2 likes
  • 4 in conversation