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")=' ');
... View more