macro program

[HeatherNewton]

%MACRO add_idx(tblname, idxname,idxlist);
%LOCAL M_IDX_CNT;
%LOCAL M_LIBNAME;
%LOCAL M_TABLENAME;
%LOCAL M_TMP1;
%LOCAL M_TMP2;

%LET M_LIBNAME=%SUNSTR(&tblname,1,%eval(%INDEX(&tblname,.)-1));
%LET M_TMP=%eval(%INDEX(&tblname,.)+1);
%LET M_TMP2=%eval(%LENGTH(&tblname)-%INDEX(&tblname,.));
%LET M_TABLENAME=%SUBSTR(&tblname,&M_TMP1,&M_TMP2);

Proc SQL;
Select count(*) as cnt INTO :M_IDX_CNT
from sashelp.vindex
where upcase(libname)=upcase("&M_LIBNAME.") and upcase(memname)=upcase("M_TABLENAME." and upcase(indxname)=upcase("%idxname.");
quit;

%IF &M_IDX_CNT=0 %THEN %DO;
PROC SQL NOPRINT;
CREATE INDEX &idxname ON &tblname(&idxlist);
QUIT;
%END;
%MEND add_idx;

Hi I have this macro in my utitlity. sas

what is this program doing?

what does %LOCAL M_IDX_CNT do? is it assigning M_IDX_CNT as a local macro variable?

what is %SUBSTR() do?

what does %eval() do?

are these sas programs not defined by user?

what is the difference between normal if then do end compare to the above one with % in front of if then do end?

is add_idx user defined? or it is a standard sas program? what kind of programs are usually added to utility.sas? 

[LinusH]

what is this program doing?

- Filterering etc based on macro variable values.

what does %LOCAL M_IDX_CNT do? is it assigning M_IDX_CNT as a local macro variable?

- It's defing M_IDX_CNT as a local macroa variable, meaning it's not available outside the macro.

what is %SUBSTR() do?

- It...substrings a string...(outside data step processing)

what does %eval() do?

- SInce macro programmaing is all about strings, the default behaviois is not to execute calculation. %eval trigger an evaluations of an expresssion.

what is the difference between normal if then do end compare to the above one with % in front of if then do end?

- % means macro logic, without it's dat step logic (managing expressions towards data in data sets/program data vector).

[ballardw]

What is this doing? Probably generating error messages about undefined macro functions

%SUNSTR

%idxname

Do you know what SASHELP.VINDEX contains? 

HOW exactly is this called? If it is not called then it does nothing.

I guess the intent is to check if there is an index on some data set and if it doesn't exist attempt to create it.

[Patrick]

@HeatherNewton 

Following your posts since a while I have to ask: Why are you not first consulting the SAS documentation before asking some of your the question?

Please don't take this as an offence! I just believe if you first look-up in the docu stuff like the %eval() function before asking "what does %eval() do?" then you might actually get more detailed information and learn more than what anyone here could provide. The Base SAS docu is quite good and detailed. And then if the SAS docu doesn't answer your question post it here. 

One needs with all vendors to learn how to find and read the documentation. That's a very important skill.

It took me for example quite a while to get myself accustomed with the Oracle documentation - but now that I've got it I can most of the time "help myself" - and this not only is efficient it also makes me feel "good and empowered".

[PaigeMiller]

@HeatherNewton 

Adding to Patrick's advice, up at the very top of every SAS web page is a magnifying glass icon that allows you to search the documentation for any particular function or PROC or statement in SAS. You can also use this link: https://support.sas.com/en/search.html?q=*%3A* (I recommend that you bookmark it).

[Tom]

The %LOCAL macro statement defines a local macro variable.  This will prevent the macro from overwriting the value of any macro variable with that name that might have already existed when the macro started running.  It is to prevent the macro from causing unwanted side effects.

%INDEX(), %SUBSTR() and %EVAL() are macro functions.  Look for the definition of macro statements and macro functions in the documentation.  https://documentation.sas.com/doc/en/vdmmlcdc/1.0/mcrolref/p0ivgabci0y2den1usf43mxdkpgp.htm

This program is defining a macro named %ADD_INX().  So %ADD_INX() is defined by the user, but other than the typo %SUNSTR(), it is not calling any other user defined macros.

The macro is testing the DICTIONARY.INDEXES metadata table to see if the index named in the macro call already exists or not.  If it does not then it creates it.  So it is way to insure that the index exist without forcing SAS to recreate one that already exists.

If would be a little easier to code and understand if it just use the %SCAN() macro function instead of the %INDEX(), %EVAL() and %SUBSTR().  Use the %UPCASE() macro function to eliminate the need to use the UPCASE() function in the generated SAS code.  You can even make the macro smart enough to handle single level table names by assuming they indicate a table in the WORK library.

%macro add_idx(tblname, idxname,idxlist);
%local m_libname m_tablename m_idx_cnt;

%let tblname=%upcase(&tblname);
%let m_tablename=%scan(&tblname,-1,.);
%let m_libname=%scan(WORK.&tblname,-2,.);

proc sql noprint;
  select count(*) into :m_idx_cnt
  from dictionary.indexes
  where libname="&m_libname"
    and memname="m_tablename"
    and upcase(indxname)=%upcase("%idxname")
  ;
%if &M_IDX_CNT=0 %then %do;
  create index &idxname on &tblname(&idxlist);
%end;
quit;
%mend add_idx;

You might even enhance to check that the list of variables in the currently defined index match the list in the input parameter &IDXLIST because in that case you might want to redefine the index to match the new list of variables.