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

[Aasth]

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

[Reeza]

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

---

[Tom]

"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.