I don't think this is really a good thing to do, but you can create a macro for you count/count distinct and then use call execute on that. In this case I'm also storing the variable names from proc contents.
%macro counts(variable, table_name);
title "Summary of &variable in table &table_name";
proc sql;
select count(&variable) as count_&variable.
from &table_name;
select count(distinct &variable) as count_dist_&variable
from &table_name;
quit;
title;
%mend;
*obtain list of variables;
proc contents data=sashelp.class out=var_list(keep=name);
run;
*execute for all variables;
data _null_;
set var_list;
table_name = 'sashelp.class';
str=catt('%counts(', name, ',', table_name, ');');
put str;
call execute(str);
run;
A slightly better method, but still not particularily useful is the following:
%macro check_distinct(variable, table_name);
title "Unique summary of &variable in table &table_name";
proc sql;
select count(&variable) as count_&variable., count(distinct &variable) as count_dist_&variable
from &table_name;
title;
%mend;
*execute for all variables;
data _null_;
set var_list;
table_name = 'sashelp.class';
str=catt('%check_distinct(', name, ',', table_name, ');');
put str;
call execute(str);
run;
I would recommend changing the macro so that:
1. The variable names created are the same throughout
2. Add variable and table name into the table
3. Append to a master table
4. Print master table for summary
So it would look like the following. I'll leave the call execute part up to you here. You'll also want to make sure you drop the table check_unique_results before running your call execute statement, especially if you run it and get errors the first time.
%macro check_unique(variable, table_name);
proc sql noprint;
create table temp as
select "&variable" as Variable length=50,
"&table_name" as Table length=50,
count(&variable) as Count,
count(distinct &variable) as count_distinct
from &table_name;
quit;
proc append base=check_unique_results data=temp force;
run;
proc sql noprint;
drop table temp;
quit;
%mend;
... View more
