topic Re: PROC SQL trouble in SAS Procedures

PROC SQL trouble

deleted_user — Tue, 17 Mar 2009 16:04:48 GMT

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

Re: PROC SQL trouble

deleted_user — Tue, 17 Mar 2009 16:14:15 GMT

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

OR

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

Same thing.

Re: PROC SQL trouble

deleted_user — Tue, 17 Mar 2009 16:21:29 GMT

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