topic Re: SAS E.G. 5.1 - How to Loop through a set of variables in SAS Programming

SAS E.G. 5.1 - How to Loop through a set of variables

JS — Thu, 07 Apr 2016 22:38:16 GMT

Hello Everyone,

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!

Re: SAS E.G. 5.1 - How to Loop through a set of variables

Reeza — Thu, 07 Apr 2016 23:44:40 GMT

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?

Re: SAS E.G. 5.1 - How to Loop through a set of variables

JS — Thu, 07 Apr 2016 23:49:19 GMT

Reeza,

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?

Re: SAS E.G. 5.1 - How to Loop through a set of variables

Reeza — Fri, 08 Apr 2016 00:18:43 GMT

http://support.sas.com/documentation/cdl/en/mcrolref/61885/HTML/default/viewer.htm#a000543697.htm

See example #2

Re: SAS E.G. 5.1 - How to Loop through a set of variables

Ksharp — Fri, 08 Apr 2016 01:40:13 GMT

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;

Re: SAS E.G. 5.1 - How to Loop through a set of variables

Reeza — Fri, 08 Apr 2016 03:21:31 GMT

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;