topic SAS Query Filter option 'Is not Missing' not working as expected in SAS Enterprise Guide

SAS Query Filter option 'Is not Missing' not working as expected

Aasth — Mon, 23 Sep 2019 14:53:39 GMT

Hi,

I am using the following query to filter out columns where NPI value is not missing :

PROC SQL;

CREATE TABLE WORK.QUERY_FOR_PROV_000A AS

SELECT DISTINCT t1.NPI,

/* COUNT_of_NPI */

(COUNT(t1.NPI)) AS COUNT_of_NPI

FROM IDSEDWP.PROV t1

WHERE t1.NPI NOT IS MISSING AND t1.PLAN_ID = '020'

GROUP BY t1.NPI

ORDER BY t1.NPI;

QUIT;

But i am getting the following result:

NPI COUNT_of_NPI

50656

0987654321 1

1003000274 2

1003000506 1

1003001553 1

1003002106 1

1003002262 1

1003002510 1

1003002700 1

1003003310 1

1003003625 2

1003003773 1

1003004284 3

1003004375 1

1003005125 1

1003005687 1

1003005752 1

1003006040 1

1003006065 1

1003006180 4

1003007840 1

Why am I still seeing the NPI columns count where the data is missing? Is there something I am doing wrong here?

Thanks,

Aastha

Re: SAS Query Filter option 'Is not Missing' not working as expected

Reeza — Mon, 23 Sep 2019 14:59:00 GMT

t1.NPI NOT IS MISSING

should be:

not missing(t1.NPI)

@Aasth wrote:

Hi,

I am using the following query to filter out columns where NPI value is not missing :

PROC SQL;

   CREATE TABLE WORK.QUERY_FOR_PROV_000A AS

   SELECT DISTINCT t1.NPI,

          /* COUNT_of_NPI */

            (COUNT(t1.NPI)) AS COUNT_of_NPI

      FROM IDSEDWP.PROV t1

      WHERE t1.NPI NOT IS MISSING AND t1.PLAN_ID = '020'

      GROUP BY t1.NPI

      ORDER BY t1.NPI;

QUIT;

But i am getting the following result:

NPI                         COUNT_of_NPI

                                50656

0987654321         1

1003000274         2

1003000506         1

1003001553         1

1003002106         1

1003002262         1

1003002510         1

1003002700         1

1003003310         1

1003003625         2

1003003773         1

1003004284         3

1003004375         1

1003005125         1

1003005687         1

1003005752         1

1003006040         1

1003006065         1

1003006180         4

1003007840         1

Why am I still seeing the NPI columns count where the data is missing? Is there something I am doing wrong here?

Thanks,

Aastha

Re: SAS Query Filter option 'Is not Missing' not working as expected

Tom — Mon, 23 Sep 2019 15:17:40 GMT

"NOT IS MISSING" seems like a strange syntax, but it looks like PROC SQL is able to interpret it as meaning the same thing as "IS NOT MISSING" or "NOT (... IS MISSING)".

If your variable numeric or character? If numeric do you have a format attached to it that is showing some values as blanks?
If it is character then perhaps the value has some other non-blank character that look like blanks in the output. Like 'A0'X or tab or line feed or carriage return.