DATA Step, Macro, Functions and more

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

Reply
Contributor JS
Contributor
Posts: 38

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

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!

 

 

 

Super User
Posts: 17,963

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

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?

Contributor JS
Contributor
Posts: 38

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

 

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?

Super User
Posts: 17,963

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

Super User
Posts: 9,691

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

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;




Super User
Posts: 17,963

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

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;

 

Ask a Question
Discussion stats
  • 5 replies
  • 258 views
  • 0 likes
  • 3 in conversation