04-07-2016 06:38 PM
I'm trying to set up a query that allows me loop through a set of variables.
For example, I am currently doing something like this:
proc contents data=TABLE1;run; proc print data=TABLE1 (obs=50);run; proc contents data=TABLE_X;run; proc print data=TABLE_X (obs=50);run;
I also do something similar in PROC SQL:
proc sql; select count(FIELD1) as count_FIELD1 from TABLE1; select count(distinct (FIELD1) as count_dist_FIELD1 from TABLE1; quit; proc sql; select count(FIELD2) as count_FIELD2 from TABLE1; select count(distinct (FIELD2) as count_dist_FIELD2 from TABLE1; quit;
Is there a way to more efficiently build out my query so that I don't have to re-write the entire thing, but just have a list of table/variable names?
Looking forward to your thoughts!
04-07-2016 07:44 PM
EG has the characterize your data task but I'm not sure exactly what it produces.
A proc freq with the nlevels option is also a good bet.
Most likely though you can create a macro, place the parameters in a data set and use call execute to execute the macro for all your variable and tables.
This mentions EG, is it safe to assume your ok coding?
04-07-2016 07:49 PM
Thank you for responding.
I primarily leverage the program function of SAS EG, so I am comfortable programming.
I have used a macro before to assign a value that is used frequently, but have never "called to execute" and looped it.
What would be the reference you are referring to?
04-07-2016 09:40 PM
Use CALL EXECUTE() : data _null_; set sashelp.vmember(obs=10 where=(libname='SASHELP' and memtype='DATA')); call execute('proc contents data=sashelp.'||strip(memname)||';run; proc print data=sashelp.'||strip(memname)||'(obs=10);run;'); run;
04-07-2016 11:21 PM
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;