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?
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.
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.