topic Re: Passing the logic for a WHERE condition in a PROC SQL using macro in SAS Programming

Passing the logic for a WHERE condition in a PROC SQL using macro

path2success — Tue, 30 Oct 2018 21:05:25 GMT

Hi,

I have a SAS data set where I have 100 variables and 200k observations. For each variable, I need to get the count of all rows that pass a specific condition. Mainly, checking the information quality for each variable. For simplicity purposes, I have created a simple data set with 3 variables (all are characters variables) and 6 observations. 3 variables are COMID_NEW, COMNAME and COMLINK.

A simple SAS data set, is in the attached document.

Below is the SAS code that I am passing through a macro –

Code 1:

%macro comchk(var=, cndtn=);

proc sql;

create table &var as

select count(&var) as &var

from test_data2

where compress(strip(&var), &cndtn., "ki") = ' '

order by 1;

quit;

proc transpose data = &var out=&var._t(rename=(_name_=name col1=fmt_check));

run;

data &var._t;

set &var._t;

format name $200.;

run;

%mend;

%comchk(var=comid_new, cndtn = "ABCDEFGHIJKLMNOPQRSTUVWXYZ`~!@#$%^&*()-_=+\|[]{};:',.<>?/ ");

%comchk(var=comname, cndtn = "0123456789`~!@#$%^&*()-_=+\|[]{};:',.<>?/ ");

%comchk(var=comlink, cndtn = "0123456789`~!@#$%^&*()-_=+\|[]{};:',<>?/ ");

This code works fine without any issue. However, what I want to achieve is, be able to pass the entire logic (in the WHERE statement) to CNDTN macro variable.

For e.g., something like what I am doing in the code below-

However, this fails and there is something that I am not doing right.

Could you please help me understand why is my 2^nd code (as below) failing?? THANKS a ton!!

Again, my question is not about how to make my code effective (suggestions are very welcome!), however my main question is how can I pass a full logic along with functions to a macro variable?

Code 2:

%macro comchk(var=, cndtn=);

proc sql;

create table &var as

select count(&var) as &var

from test_data2

where &cndtn.

order by 1;

quit;

proc transpose data = &var out=&var._t(rename=(_name_=name col1=fmt_check));

run;

data &var._t;

set &var._t;

format name $200.;

run;

%mend;

%comchk(var=comid_new, cndtn = (compress(strip(comid_new), "ABCDEFGHIJKLMNOPQRSTUVWXYZ`~!@#$%^&*()-_=+\|[]{};:',.<>?/ ", "ki")=' ');

%comchk(var=comname, cndtn = (compress(strip(comname) = "mycompany");

%comchk(var=comlink, cndtn = (compress(strip(comlink), "0123456789`~!@#$%^&*()-_=+\|[]{};:',<>?/ ", "ki")=' ');

Re: Passing the logic for a WHERE condition in a PROC SQL using macro

Reeza — Tue, 30 Oct 2018 21:34:33 GMT

Are you sure that first one works? It's not working for me.

Re: Passing the logic for a WHERE condition in a PROC SQL using macro

ballardw — Tue, 30 Oct 2018 22:32:24 GMT

Instead of creating hundreds of tables why not try 1 table and use a better procedure to count.

The below adds one line an one variable to a new version of the data set and provides a label.

The label could be more descriptive if you have multiple tests involving the same variable.

This approach has a couple of other advantages: you are much less likely to have issues with passing values as macro parameters,

Much easier to write code that will test multiple ranges, referential values (dependent on another variable or two), and a whole lot more compact code to begin with.

Then Proc Freq counts 0 and 1. The 1 are true.

Or use a format to show True/False or Yes/no for the 1/0 values.

data example;
   set sashelp.class;
   condition1= (sex='F');
   condition2= (height > 60);
   condition3= (weight < 120);
   label 
   condition1= 'Sex condition true'
   condition2= 'Height condition true'
   condition3= 'Weight condition true'
   ;
run;

proc freq data=example;
   tables condition: ;
run;

Or a more compact report

proc tabulate data=example;
   var condition: ;
   table condition: ,
         sum='Count'*f=best6.
   ;
run;

If you haven't seen the : list that says basically "place all variables whose names start with this string in to the list here".

Re: Passing the logic for a WHERE condition in a PROC SQL using macro

ChrisNZ — Tue, 30 Oct 2018 22:44:23 GMT

Unbalanced parentheses?

This works fine:


%macro comchk( cndtn );

proc print data =SASHELP.CLASS;
  where ( &cndtn. );
run;
 
%mend;
 
%comchk( cndtn = compress(strip(NAME), "ABCDEFGHIJKLMNOPQRSTUVWXYZ`~!@#$%^&*()-_=+\|[]{};:',.<>?/ ", "ki")=' ' );