PROC SQL trouble

[deleted_user]

Hi,

I am new to SAS (user for approx 5 months now, getting better every day!) and have encountered a snag in a query I am trying to execute.

I am attempting to count the number of rows in a table but do not want to include rows that include certain values of a particular column. In my case, the column is 'inactive_code' and I want to make sure I am not counting any records marked inactive due to the individual being deceased ('D') or moved ('M').

I have tried creating an SQL statement such as:
proc sql;
select count (*)
from table.test
where pat_birth_date >='01JAN2003'd
and pat_birth_date <='31AUG2008'd
and inactive_code not in ('D', 'M');
quit;

but it does not return the correct values and I'm not sure why.

I have also explored a function in Oracle called "NVL" but it apparently does not exist in PROC SQL as SAS responds that it does not recognize the function NVL.

Attempting to manually do what NVL does (recode null variables, I think) is out of the question as the table I am querying is read-only for me and contains more than 1,000,000 records.

The only way I have found to overcome the issue is to do three separate queries

Query 1: select all persons in the table that have inactive_code='D'
Query 2: select all persons in the table that have inactive_code='M'
Query 3: select all persons in the table (regardless of inactive_code)

Then take the result from #3 and subtract 1+2...

Any better way to do this? Please be gentle, I am a novice at best... Message was edited by: rocknroll_ghost

[deleted_user]

where inactive_code not in ('D' 'M');

OR

where inactive_code ne 'D' and inactive_code ne 'M';

Same thing.

[deleted_user]

Sorry, for some reason my message was truncated--

I have tried creating an SQL statement such as:
proc sql;
select count (*) from table.test
where pat_birth_date >='01JAN2003'd
and pat_birth_date <='31AUG2008'd
and inactive_code not in ('D', 'M');
quit;

but it does not return the correct values and I'm not sure why.

I have also explored a function in called "NVL" but it apparently does not exist in PROC SQL as SAS responds that it does not recognize the function NVL.

Attempting to manually do what NVL does (recode null variables, I think) is out of the question as the table I am querying is read-only for me and contains more than 1,000,000 records.

The only way I have found to overcome the issue is to do three separate queries

Query 1: select all persons in the table that have inactive_code='D'
Query 2: select all persons in the table that have inactive_code='M'
Query 3: select all persons in the table (regardless of inactive_code)

Then take the result from #3 and subtract 1+2...

Any better way to do this? Please be gentle, I am a novice at best... Message was edited by: rocknroll_ghost