topic Re: Help understanding what is happening in this macro? in New SAS User

Help understanding what is happening in this macro?

ivymckeeSTC — Tue, 22 Dec 2020 14:19:15 GMT



%MACRO dropEmptyFlags(dataset_name);
        DATA WORK.flag_names (KEEP=name);
                SET SASHELP.VCOLUMN;
                WHERE ( libname='WORK' AND UPCASE(memname)=UPCASE("&dataset_name") ) AND ( UPCASE(name) LIKE 'CE_%' OR UPCASE(name) LIKE 'SM_%');
        RUN;
        PROC SQL;
                SELECT DISTINCT NAME INTO :flags SEPARATED BY ' '
                FROM WORK.flag_names;
        QUIT;
        %LET flag_list=&flags;
        %LET drop_list=%STR();
        %DO i=1 %TO %SYSFUNC(COUNTW(&flags));
                %LET flag=%SCAN(&flags,&i);
                PROC SQL;
                        SELECT COUNT(&flag) INTO :triggered
                        FROM WORK.&dataset_name
                        WHERE &flag=1;
                QUIT;
                %IF &triggered=0 AND drop_list NE %STR() %THEN; %LET drop_list=%SYSFUNC(CATX(%str( ),&drop_list,&flag)) %ELSE %IF &triggered=0 %THEN %LET drop_list=&flag;
        %END;
        %PUT drop list is %SYSFUNC(COUNTW(&drop_list)) long out of %SYSFUNC(COUNTW(&flags)): SET WORK.&dataset_name(DROP=&drop_list);
%MEND;

Hi there,

I inherited this code from a colleague and am trying to understand what it does. I do not understand where the data is coming from for this macro, first and foremost, as work.Flag_names does not exist in my work library after running this code and doesn't exist anywhere else in the program. Could anybody help me decode this?

Thank you!

Re: Help understanding what is happening in this macro?

Astounding — Tue, 22 Dec 2020 15:04:18 GMT

I can give you a step or two, but you are really in over your head. You would need to understand more SAS (not macro language) to figure out what is happening here. For example, you are wondering where work.flag_names comes from when the answer is that the program creates it. So more SAS knowledge is mandatory. More macro language experience would also be needed. For example, did you know that running this code does not execute anything? It merely defines what the command %dropEmptyFlags should do. To execute anything, you need to later execute %dropEmptyFlags.

When you execute %dropEmpty_Flags, you supply a value for DATASET_NAME. This should be the name of an existing data set in the WORK library. From that data set, the program creates FLAG_NAMES in the WORK library, containing the names of all variables that begin with "CE_" or "SM_". (Capitalization is not important.)

That takes you through the first half of the program. Good luck in your quest.

Re: Help understanding what is happening in this macro?

GertNissen — Tue, 22 Dec 2020 15:32:02 GMT

Hi @ivymckeeSTC

Actually, the macro doesn't do much - It looks like a small utility that output text in the SAS Log like "drop list is 1 long out of 2: SET WORK.class(DROP=SM_weight)"

Perhaps the intention is to copy this text "SET WORK.class(DROP=SM_weight)" and run it manually afterwards to clean up the dataset ?

The Macro does not need a dataset named flag_name, as it's generated by the macro.

It just checks if your work dataset contains a column that named with CE_ or SM (in the beginning of the column name) and if they contain a value of 0.

Perhaps if you run this, you will understand the macro a little better. - look for "drop list" in the SAS Log.

%MACRO dropEmptyFlags(dataset_name);
        DATA WORK.flag_names (KEEP=name);
                SET SASHELP.VCOLUMN;
                WHERE ( libname='WORK' AND UPCASE(memname)=UPCASE("&dataset_name") ) AND ( UPCASE(name) LIKE 'CE_%' OR UPCASE(name) LIKE 'SM_%');
        RUN;
        PROC SQL;
                SELECT DISTINCT NAME INTO :flags SEPARATED BY ' '
                FROM WORK.flag_names;
        QUIT;
        %LET flag_list=&flags;
        %LET drop_list=%STR();

        %DO i=1 %TO %SYSFUNC(COUNTW(&flags));
                %LET flag=%SCAN(&flags,&i);
                PROC SQL;
                        SELECT COUNT(&flag) INTO :triggered
                        FROM WORK.&dataset_name
                        WHERE &flag=1;
                QUIT;
                %put triggered=&triggered;
                %IF &triggered=0 AND drop_list NE %STR() %THEN %LET drop_list=%SYSFUNC(CATX(%str( ),&drop_list,&flag)); %ELSE %IF &triggered=0 %THEN %LET drop_list=&flag;
        %END;

        %PUT drop list is %SYSFUNC(COUNTW(&drop_list)) long out of %SYSFUNC(COUNTW(&flags)): SET WORK.&dataset_name(DROP=&drop_list);
%MEND;

data work.class;
  set sashelp.class;
  IF height =  59 then CE_height=1;
  SM_weight=0;
run;

%dropEmptyFlags(class);


data work.class;
  set sashelp.class;
  if weight = 90 then SM_weight=1;
run;

%dropEmptyFlags(class);

You will find these two lines somewhere in your SAS LOG.

drop list is 1 long out of 2: SET WORK.class(DROP=SM_weight)
drop list is 0 long out of 1: SET WORK.class(DROP=)

Re: Help understanding what is happening in this macro?

Tom — Tue, 22 Dec 2020 15:21:12 GMT

I don't think this code will do anything. The code as posted has both syntax errors and logic errors.

It appears to be an attempt to generate a list of variables to drop by counting how many times the variable has a value of 1.

The macro is creating macro variables without ever making sure they are defined external to the macro. So macro variables like DROP_LIST will be created as local if they have not already been created before the macro is called. You might add this to the top of the macro:

%local flag_list i flag triggered  ;
%if not %symexist(drop_list) %then %global drop_list ;

This line has misplaced semi-colons:

%IF &triggered=0 AND drop_list NE %STR() %THEN; %LET drop_list=%SYSFUNC(CATX(%str( ),&drop_list,&flag)) %ELSE %IF &triggered=0 %THEN %LET drop_list=&flag;

Perhaps they meant:

%IF &triggered=0 AND drop_list NE %STR() %THEN %LET drop_list=%SYSFUNC(CATX(%str( ),&drop_list,&flag));
%ELSE %IF &triggered=0 %THEN %LET drop_list=&flag;

But really all they needed was:

%IF &triggered=0 %THEN %LET drop_list=&drop_list &flag;

There is no need to try to use any of the CAT series functions in macro code. Just expand the macro variables where you want their values to appear.

There is no need to use UPCASE() function on the LIBNAME or MEMNAME fields in SASHELP.VCOLUMN as those values are always upper case.