topic Re: Help with Where/Clause in SAS Procedures

Help with Where/Clause

antoniodneto — Tue, 31 Mar 2020 18:20:17 GMT

Hi guys,

I have one table with many where clauses. I need to count a number of cases for each where clause.

For example:

apply filter 1 how many cases was filtered, then I apply filter 2 and count the cases again.

Thank you guys!!

Re: Help with Where/Clause

Shmuel — Tue, 31 Mar 2020 18:58:02 GMT

Am I right, you want to count number of observations (cases) per different conditions (where clause).

You can do it in one step using IF statements:

data want;
  set have(end=eof);
      retain count1 count2 ...;
      if <condition-1> then count1+1;
      if <condition-2> then count2+1;
     ....
     if eof then do;
       output;  /* save result in a data set */
       put count1= count2= ...;  /* print result in log */
    end;
    keep count1 count2 ...;
run;

Re: Help with Where/Clause

antoniodneto — Tue, 31 Mar 2020 19:08:46 GMT

Hi Shmuel,

Can I do it into a PROC SQL statement, because I have some joins inside.

Tks

Re: Help with Where/Clause

Shmuel — Tue, 31 Mar 2020 19:23:19 GMT

Please post:

1) a test data of your table

2) a pseudo code to show what you mean by join and some typical conditions

3) relating to test data - show required results

Re: Help with Where/Clause

antoniodneto — Tue, 31 Mar 2020 19:32:52 GMT

PROC SQL;
CREATE TABLE TESTE AS
SELECT DISTINCT T1.*
FROM TESTE_2 T1

INNER JOIN TESTE_3 T2 ON T1.NAME = T2.NAME
INNER JOIN TESTE_4 T3 ON T1.NAME = T3.NAME
LEFT JOIN TESTE_5 t4 ON T1.NAME = T4.NAME
LEFT JOIN TESTE_6 T5 ON T1.NAME = T5.NAME

WHERE

T3.NAME IS MISSING AND
T4.NAME IS MISSING AND
T5.NAME IS MISSING

;
RUN;

That is my sample, So I need to count the cases for each filter:

Apply T3.NAME IS MISSING (count how many cases out for this filter) then apply the second (count again)....

It helps?

Thank you so much!

Re: Help with Where/Clause

Kurt_Bremser — Wed, 01 Apr 2020 08:55:03 GMT

You cannot count the discrete missing conditions from the joined table (because of the "AND").

You need to get the counts separately for each joined table.

Hint: because of the inner join, t3.name can only be missing if t1.name is missing.

Re: Help with Where/Clause

antoniodneto — Wed, 01 Apr 2020 13:06:34 GMT

Hi @Shmuel,

Can you still help me?

Tks!!!

Re: Help with Where/Clause

Shmuel — Wed, 01 Apr 2020 14:10:39 GMT

Try do it with 2 steps:

1) use sql to create the joins, without any where clause for counting

2) use the data step I posted as example