PROC FREQ: Specify list of variables for multiway table

[JacquesR]

I use PROC TRANSPOSE to transpose a data set (variable: Proper_ingredient). I never know what the resulting number of transposed columns from one run to the next will be (typically, though, 2-9).

When I want to get frequencies on the transposed columns, I know I can do this:

PROC FREQ
    COMPRESS
    DATA=WORK.PoisonComparison
    ORDER=FREQ;
    TABLES strPoisonTaken Proper_ingredient: /NOCUM MISSING;
RUN;

That will give me all the oneway tables needed.

But what if I want a multiway table:

PROC FREQ
    COMPRESS
    DATA=WORK.PoisonComparison
    ORDER=FREQ;
    TABLES strPoisonTaken*Proper_ingredient1*Proper_ingredient2*...*Proper_ingredientx /LIST NOCUM MISSING;
RUN;

Is there a simple way to instruct SAS to create that listing?

[mkeintz · Accepted solution]

You can build a macrovar of the form proper_ingredient1*proper_ingredient2*…. using the dictionary.columns table in sql (untested below):

proc sql noprint;
   select distinct name into :varlist separated by '*'
   from dictionary.columns
   where libname ='WORK' and upcase(memname)='POISONCOMPARISON'
    and upcase(name) like 'PROPER^_INGREDIENT%' escape '^' ;
quit;
proc freq compress data=work.poisoncomparison order=freq;
  tables strpoisontaken*&varlist / list nocum missing;
run;

Thanks to @andreas_lds , I've corrected the above to account for the "_" wild character. 

[andreas_lds]

You can use proc sql to get the list:

proc sql noprint;
	select Name 
		into :list separated by '*'
		from sashelp.vcolumn
			where libname = 'WORK' 
				and memname = 'POISONCOMPARISON' 
				and lowcase(name) like 'proper^_ingredient%' escape '^'
	;
quit;

proc freq
      compress
      data= work.PoisonComparison
      order= freq;
    tables strPoisonTaken*&list. / list nocum missing;
run;

In sashelp.vcolumn library-names and dataset-names are in upcase. In SQL the underscore is a wildcard for one char, so it should be escaped.