BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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
2 REPLIES 2
deleted_user
Not applicable
where inactive_code not in ('D' 'M');

OR

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

Same thing.
deleted_user
Not applicable
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 546 views
  • 0 likes
  • 1 in conversation