Macro loop to generate Proc Tabulate by origin

Question

Using SASHELP.CARS, I would like to make a PROC TABULATE by Origin. So, the first way is to make 3 PROC TABULATE such as :

    PROC TABULATE DATA = data out=tabulate;
    where Origin="Asia";
    CLASS Make DriveTrain   ; 
    TABLE (Make), (DriveTrain)   / nocellmerge ; 
    run;

But, instead, I would like to automatize this in a macro loop (here is a simple example I made. The real database I work with is more complex; this is why I need to make a macro :). Could you please help me why the following code won’t work ? It’s the « where Origin=reg; » part that seems to be the problem. Thank you ! So here is my code:

data data; set sashelp.cars;run;

data classes;
input id_reg reg_name $ ; 
cards;
1 Asia 
2 Europe 
3 USA 
run;

%macro comp;
%local i reg;

    %do i=1 %to 3;

        proc sql ;
        select reg_name
        into
        :reg_name 
        from classes
        where id_reg = &i.;
        quit;
        %let reg=reg_name;

        PROC TABULATE DATA = data out=tabulate_&i;
        where Origin=reg;
        CLASS Make DriveTrain   ; 
        TABLE (Make), (DriveTrain)   / nocellmerge ; 
        run;

    %end;
%mend comp;
%comp

Answer 1

Thank you very much! Here is the code that works:

data data; set sashelp.cars;run;

data classes;
input id_reg reg_name $ ; 
cards;
1 Asia 
2 Europe 
3 USA 
run;

%macro comp;
%local i ;

    %do i=1 %to 3;

        proc sql ;
        select reg_name
        into
        :reg_name 
        from classes
        where id_reg = &i.;
        quit;

        PROC TABULATE DATA = data(where=(Origin="&reg_name")) out=tabulate_&i;
        CLASS Make DriveTrain   ; 
        TABLE (Make), (DriveTrain)   / nocellmerge ; 
        run;

    %end;
%mend comp;
%comp

Answer 2

If you insist on using Macro, the correct statement will be generated by double quoting the macro variable resolution so as to inject a string literal into the submit stream.

  where Origin="&reg";

Answer 3

Use a BY statement to independently process like grouped subsets of a data set. Use a WHERE statement to select the subset(s) to process.

Example:

ods html file='output.html' style=plateau;

proc sort data=sashelp.cars out=cars_sorted;
  by origin;
run;
  
title;footnote;
options nocenter nodate nonumber;

PROC TABULATE DATA=cars_sorted;

    by origin;
    where Origin in ("Asia", "Europe", "USA");

    where also make >= 'P';   * further subset for reduced size of output screen shot;


    CLASS Make DriveTrain   ; 
    TABLE (Make), (DriveTrain)   / nocellmerge ; 

run;

ods html close;

Output

Alternatively, use a TABLE statement of form <page dimension>,<row dimension>,<column dimension> in lieu of BY group processing. Such a form does not need presorted data because the is constructed from CLASS variables.

Example:

PROC TABULATE DATA=sashelp.cars;   /* original data, not sorted */

    where Origin in ("Asia", "Europe", "USA");

    where also make >= 'P';   * further subset for reduced size of output screen shot;

    CLASS Origin Make DriveTrain   ;  /* Origin added to CLASS */

    TABLE Origin, (Make), (DriveTrain)   / nocellmerge ;  /* Origin is page dimension */

run;

Output