Macro with proc tabulate

0
votes

I want to write a code where under certain condition a different proc tabulate will be executed. The problem is somewhere inside macro. Without changes everything worked just fine. The idea of code is quite simple but it is the first time I have ever made something like that. Depending on value x in data makro different macro will be executed. I do not know how variables should be defined inside the macro.

data makro;
set number;
if number < 20 then x=1; 
else x=2;
run;

  proc format;
value temp 70- HIGH='red';
run;

PROC SQL;
   CREATE TABLE Stat_for_&cel AS 
   SELECT distinct t1.&zmienna, 
          t1.&cel,
t2.number,
               (COUNT(t1.cid)) AS ILE
      FROM zrodlo.abt_app t1 left join ile_zmiennych t2 on t1.&zmienna=t2.&zmienna where t1.&zmienna not is missing 
      GROUP BY t1.&zmienna,
               t1.&cel
      ORDER BY t1.&zmienna DESC;
QUIT;

%macro tabelka1(Statystyka_dla_cel,&ILE,&cel,&zmienna);

proc format;
value temp 70- HIGH='red';
run;

PROC TABULATE
DATA= &&Stat_for_&cel format=commax10.2 ; 

    VAR &&ILE;

    CLASS &&zmienna/ MISSING;
    CLASS &&cel/ MISSING;

    TABLE 

    /* Row Dimension */
&&cel,
/* Column Dimension */
&&ILE*  ColPctSum*    &&zmienna*[style=[background=temp.]];

RUN;
%mend tabelka1; 

data makro_2;
set makro;
if x=1 then call execute ('%tabelka1');
run;

EDIT: Added information from 'answer' as it's not an answer.

code should work like that . &Cel and &zmienna are just variables which i want to use in proc tabulate procedure and which are defined manually at the beginning of the code. I want to run macro just once when 'number' is <20. In next few steps i just prepare data with proc format and also create a table 'Stat_for_&cel' which is later used as a data in proc tabulate procedure.

Main problem is inside the %macro tabelka1 , I believe. I do not know how should I implement the variables &cel and &zmienna inside the macro.

3
sassas-macro
Unfortunately your question isn't clear enough to understand what exactly you're asking for help with. - Reeza
Sample input and output data would help. Do you want to run the macro multiple times if there are multiple observations in MAKRO that satisfy the condition X=1 ? Or do you want to run it only once if ANY observation has X=1? Or perhaps only if ALL observations have X=1? - Tom
Here's an example of how I'd write a macro. At the bottom of the code there's sample code to execute the macro. Note the parameter list in comments and usage. - Reeza

3 Answers

0
votes

I think you're mixing up parameters in the definition of a macro and then the actual usage. You've defined your macro incorrectly. Rather than:

%macro tabelka1(Statystyka_dla_cel,&ILE,&cel,&zmienna);

It should be:

%macro tabelka1(Statystyka_dla_cel,ILE,cel,zmienna);

And then when you call the macro or try and use it, the macro variables can be passed. 

%tabelka1(Statystyka_dla_cel,&ILE,&cel,&zmienna);

Personally, I prefer to have an = sign to help highlight the difference between the parameter names and values being used. Here's a quick example:

 %macro print_vars(dsin= , vars= );

 proc print data=&dsin;
 var &vars;
 run;

 %mend;

Then you could call the macro as:

%print_vars(dsin=sashelp.class, vars= name age);
0
votes

It's really important to have a clear understanding of what various terms mean. A macro variable is a data storage location, with a name and a value. A macro variable name is something like 'ile', 'cel', etc. But be clear that a macro variable's name does not include an ampersand!! There are various ways to access the value of a macro variable, all of which involve using its name. The most common way is to use &<macro variable name>, but you could also use (in a data step) x = symget('<macro variable name>') for example - no ampersand here.

-1
votes

There is nothing wrong with defining your macro without any parameters. You can just reference the macro variables in the macro code and leave it to the user of the macro to make sure that the macro variables are defined before you invoke the macro.

%macro tabelka1;
proc format;
value temp 70- HIGH='red';
run;

PROC TABULATE DATA=Stat_for_&cel format=commax10.2 ; 
  VAR &ILE;
  CLASS &zmienna/ MISSING;
  CLASS &cel/ MISSING;
  TABLE &cel,&ILE*ColPctSum*&zmienna*[style=[background=temp.]];
RUN;
%mend tabelka1;

Then to conditionally call the macro you could use a data step.

data _null_;
  set makro;
  if x=1;
  call execute('%nrstr(%tabelka1);');
  stop;
run;