BookmarkSubscribeRSS Feed
JS
Obsidian | Level 7 JS
Obsidian | Level 7

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!

 

 

 

5 REPLIES 5
Reeza
Super User

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?

JS
Obsidian | Level 7 JS
Obsidian | Level 7

 

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?

Ksharp
Super User
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;




Reeza
Super User

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;

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 1496 views
  • 0 likes
  • 3 in conversation