topic Re: proc sql 'where' filter not being applied inside macro in SAS Programming

proc sql 'where' filter not being applied inside macro

rasmuslarsen — Thu, 29 Apr 2021 10:29:21 GMT

Dear everyone

The following macro mostly works:

It extracts the tables defined.

However, the "where" clause in the "proc sql" does not have any effect. So the problem is that rows are extracted which do not comply with the "where" clause.

For example rows are returned which have ATC = "A0".

Do anyone have any ideas what I am doing wrong?

(I have highlighted the part of the code which does seem to have the desired effect, i.e. it does not ensure that only the described ATC-codes are included)

proc sql;
select memname into :LMDB_names separated by ''
    from dictionary.tables where libname = "RAWDST"
        and memname like "LMDB%"
;quit;      
%let LMDB_len = &sqlobs;
%let LMDB_vars = pnr atc eksd;
     
%macro extract_LMDB(table_list, list_len, col_names);   
        %do i=1 %to &list_len;
        data out.%scan(&table_list, &i);
        merge out.pop (in=in_pop) pdb.%scan(&table_list, &i) (in = in_lmdb);
        by pnr;
        if in_pop = 1 & in_LMDB = 1;
        keep &col_names;
        run;     
        
        proc sql;
        create table out.%scan(&table_list, &i) as        
        select distinct
        pnr, atc,  eksd
        from out.%scan(&table_list, &i)
        
        where      
        upper(atc) like "G%" or
        upper(atc) like "D06%" or
        upper(atc) like "H02AB%" or
        upper(atc) like "G03%" or
        upper(atc) like "L04%"    
        ;quit;
        %end; 
        %mend extract_LMDB;

Re: proc sql 'where' filter not being applied inside macro

FreelanceReinh — Thu, 29 Apr 2021 10:00:21 GMT

Hello @rasmuslarsen,

@rasmuslarsen wrote:

The following macro mostly works:

(...)
%macro extract_LMDB(table_lidt, list_len, col_names);   
        %do i=1 to &list_len;
...

I don't believe that this macro even compiles. Whenever I forget the percent sign in the "%to" keyword (and this is my most frequent typo when writing macro code) I obtain the error messages

ERROR: Expected %TO not found in %DO statement.
ERROR: A dummy macro will be compiled.

Moreover, there seems to be a typo table_lidt in the first macro parameter (given that &table_list is used in the macro).

So maybe the non-compliant ATC values are from an earlier version of your code. The syntax in your WHERE clause should not cause any problem.

Re: proc sql 'where' filter not being applied inside macro

rasmuslarsen — Thu, 29 Apr 2021 10:34:03 GMT

Thanks, you are correct. The missing '%' and misspelled word came because I had typed the program manually instead of copy-pasting, because I do not have access to copy pasting on the remote machine I am using.

Thanks, for pointing out that there should be no problem with the 'where-clause', I will look else where for the problem then 🙂

Re: proc sql 'where' filter not being applied inside macro

Kurt_Bremser — Thu, 29 Apr 2021 10:55:24 GMT

Run a PROC FREQ on atc to get a comprehensive list of values there. It might be that you only have values that match at least one of the conditions in the WHERE.

Re: proc sql 'where' filter not being applied inside macro

ballardw — Thu, 29 Apr 2021 15:03:02 GMT

Minor bit of comment but "Like "G%" " will also return the values from "Like "G03%" ;