Using the same condition for multiple variables in PROC SQL statement on SAS

Question

I work with big tables with hundreds of columns. And I keep on running into a problem where multiple columns used as flags (having either a value of 1 or missing) need to be checked for the same condition. For a simplified example,

proc sql;
    create table Applications as
    select t1.ApplicantID
         , t1.ApplicationDate
         , t2.Segment
    from Applicants as t1 inner join
         Segmentation as t2 ON t1.ApplicantID = t2.ApplicantID
    where t1.ApplicantID ^= ''
          AND (t1.TR_flag = '') AND (t1.TL_flag = '') 
          AND (t1.RU_flag = '') AND (t1.RP_flag = '') 
          AND (t2.OP_flag = '') AND (t2.OU_flag = '')
          AND (t2.GG_flag = '') AND (t2.GK_flag = '')
    order by t1.ApplicantID
;
quit;

Hence, I was wondering if there is any clean and short way to check for this condition for all the flag variables in one step. I do realize I could go for a loop with an array option but it seems too complicated for such a simple problem. I could also concatenate all the variables into one string and use the condition on the new string but this solution is not universal. Basically what I am looking for is something like the IN operator but going into an opposite direction, for imaginary example,

    where t1.ApplicantID ^= ''
          AND (t1.TR_flag, t1.TL_flag, t1.RU_flag, t1.RP_flag,
               t2.OP_flag, t2.OU_flag, t2.GG_flag, t2.GK_flag) = ''

which would be easier on the eye and in best case scenario faster to run.

mjsqu mjsqu · Accepted Answer · 2015-11-10T10:23:41

You can use CMISS, along with the number of arguments you require to be missing:

CMISS (t1.TR_flag, t1.TL_flag, t1.RU_flag, t1.RP_flag,
               t2.OP_flag, t2.OU_flag, t2.GG_flag, t2.GK_flag) = 8

For specific values, and not interfering with the join:

(t1.TR_flag = t1.TL_flag = t1.RU_flag = t1.RP_flag = '1'
AND
t2.OP_flag = t2.OU_flag = t2.GG_flag = t2.GK_flag = '1')

Still, I can't see this being any faster to run.

Using the same condition for multiple variables in PROC SQL statement on SAS

1 Answers