Creating loop for proc freq in SAS

Question

I have the following data

DATA HAVE;
input yr_2001 yr_2002 yr_2003 area;
cards;
1 1 1 3
0 1 0 4
0 0 1 3
1 0 1 6
0 0 1 4
;
run;

I want to do the following proc freq for variable yr_2001 to yr_2003.

proc freq data=have;
table yr_2001*area;
where yr_2001=1;
run;

Is there a way I can do it without having to repeat it for each year, may be using a loop for proc freq??

Answer 1

Two ways:

1. Transpose it

Add a counter variable to your data, n, and transpose it by n area, then only keep values where the year flag is equal to 1. Because we set an index on the transposed group year, we do not need to re-sort it before doing by-group processing.

data have2;
    set have;
    n = _N_;
run;

proc transpose data=have 
               name=year
               out=have2_tpose(rename = (COL1 = year_flag) 
                               where  = (year_flag = 1)
                               index  = (year)
                               drop   = n
                              );

    by n area;
    var yr_:;
run;

proc freq data=have2_tpose;
    by year;
    table area;
run;

2. Macro loop

Since they all start with yr_, it will be easy to get all the variable names from dictionary.columns and loop over all the variables. We'll use SQL to read the names into a |-separated list and loop over that list.

proc sql noprint;
    select name
         , count(*)
    into  :varnames separated by '|'
        , :nVarnames
    from dictionary.columns
    where     memname = 'HAVE'
          AND libname = 'WORK'
          AND name LIKE "yr_%"
    ;
quit;

/* Take a look at the variable names we found */
%put &varnames.;

/* Loop over all words in &varnames */
%macro freqLoop;
    %do i = 1 %to &nVarnames.;
        %let varname = %scan(&varnames., &i., |);

        title "&varname.";

        proc freq data=have;
            where &varname. = 1;
            table &varname.*area;
        run;

        title;
    %end;
%mend;
%freqLoop;