Macro with PROC FREQ

[kevinmc87]

Hi,

I have a basic macro variable question, as my macro skills are underdevloped. I am trying to do a PROC FREQ with many different where clauses, and I'd like to design a macro to take care of it. So far, I've selected all values for county using:

PROC SQL;
SELECT DISTINCT COUNTY INTO :COUNTY
FROM BLAH;
QUIT;

Having this list of values for county, I want to run a PROC freq using a WHERE= statement for each value of county. I tried this but it didn't work:

%MACRO BIAS;
PROC FREQ DATA=BLAH;
TABLES P;
WHERE BLAH=&COUNTY;
%MEND;
%BIAS;

Can anyone tell me how to run this? thanks.

[Reeza]

1. Know when to use a macro and when not to!

Don't use a macro for that, use a BY statement. 

Proc sort data = have;
by county;
 
proc freq data = have;
by county;
tables p;
run;

2. Assuming for learning purposes, you'll need to include macro parameters in your macro definition. Additionally, when your using your WHERE clause, make sure to use double quotes for the macro variable to resolve.

%macro bias(county);
 
proc freq....
 
WHERE COUNTY = "&county";
run;
 
%mend;

%bias(AK);

3. Use call execute to call a macro multiple times, especially if the parameters are in a data set. The documentation has some good examples.

proc sort data=have; 
by county;
run;


data _null_;
by county;

if first.county then do;
    call execute('%bias('||county||');');
end;

run;

EDIT: edited for clarity and formatting for code. 

[ballardw]

If I did not need the cumulative percent results from proc freq, (I'm usually looking at counts or cell percents) I would consider adding a variable that indicated the "where" value for each I was interested in. Here is one example of what I mean.

data example;
   set SAShelp.cars;
   where1 = (type in ('Sedan','Sports'));
   where2 = (drivetrain = 'Front');
   label
      where1 = 'Sedans and Sports cars'
      where2 = 'Front Wheel Drive'
   ;
run;

Proc format library=work;
value TempYN
   1='Yes'
   0='No';
run;

proc tabulate data=example;
   class where1 where2 make;
   format where1 where2 TempYn.;
   table where1 where2,
         make,
         n colpctn
         ;
run;

So with two passes through the data (one to add the variables) and one in the tabulate I create 4 tables with the statistices in common interest. Create use of proc format could get better wording. I could actually create an output data set of these results and filter that to re-display just the "yes" or "no" bits as needed as well. Or move the "where" varaibles from page dimension as shown to columns and have direct comparisons between the yes and no groups available.

To apply where clauses you will need to go through the data each time. If you are doing "many" where clauses this could add up to significant processing time for large data sets.

[Cynthia_sas]

Hi:

  Modified your code a bit to just deal with Drivetrain. Why not just build user-defined formats? If you run this code, I think you'll see the difference. I have one format for Front Wheel Drive and one format for Rear Wheel Drive.

cynthia

Proc format;
value $fwd 'Front' = 'Yes'
           other = 'No';
 
 value $rwd 'Rear' = 'Yes'
            other='No';
 run;
     
proc tabulate data=sashelp.cars;
  title 'Front Wheel Drive';
   class drivetrain make;
    table drivetrain='Front Wheel Drive',
         make,
         n colpctn ;
    format type $typ. drivetrain $fwd.;
run;
 
proc tabulate data=sashelp.cars;
  title 'Rear Wheel Drive';
   class  drivetrain make;
    table drivetrain='Rear Wheel Drive',
         make,
         n colpctn ;
    format type $typ. drivetrain $rwd.;
run;