topic Re: Selecting all records based on a specific value for a variable. in SAS Programming

Selecting all records based on a specific value for a variable.

deleted_user — Wed, 25 Mar 2009 12:38:04 GMT

My table has 2 variables, ID and AMOUNT. An individual can occur several times with different amounts. If at least one of an individuals amount is greater than 0 then all records should be kept for that individual in the new table.

Old table

ID AMOUNT
1 0
2 0
2 1
2 0
3 0
3 0
4 1
4 1
4 0
4 1
5 0
6 0
6 1

should become

New table

ID AMOUNT
2 0
2 1
2 0
4 1
4 1
4 0
4 1
6 0
6 1

Is PROC SQL the appropriate procedure to use? I need some help.

Susan

Re: Selecting all records based on a specific value for a variable.

deleted_user — Wed, 25 Mar 2009 12:55:46 GMT

Yes, proc sql would be fine but you can use a data step to do this too:

data new_table;
where amount gt 0;
set old_table
run;

or

proc sql;
create table new_table as
select *
from old_table
where amount >0
;
quit;

Re: Selecting all records based on a specific value for a variable.

deleted_user — Wed, 25 Mar 2009 13:19:48 GMT

There was a very similar request yesterday:

http://support.sas.com/forums/thread.jspa?threadID=5281&tstart=0

I think you want something like:

proc sql;
create table new_table as
select * from old_table
where id in (select distinct id from old_table where amount>0);
quit;

Re: Selecting all records based on a specific value for a variable.

deleted_user — Wed, 25 Mar 2009 13:23:24 GMT

Yup, I agree - having mis-read the original question! :@(

Re: Selecting all records based on a specific value for a variable.

deleted_user — Thu, 26 Mar 2009 13:00:42 GMT

Thank you, pznew. That worked fine.

If my table hade one more variable, F, and looked like this after the PROC SQL:

ID F AMOUNT
2 A 0
2 A 1
2 B 0
4 B 1
4 A 1
4 A 0
4 A 1
6 B 0
6 A 1

Then I would like to get those where the combination of ID and F is the same as the the one where the 0 was, like this:

2 A 0
2 A 1
2 B 0
*** Want deleted because there is no 4 B 0 after the first PROC SQL
4 A 1
4 A 0
4 A 1
6 B 0
*** Want deleted because there is no 6 A 0 after the first PROC SQL

And F is not restricted to just A and B.

I don't think all this could be done in the first PROC SQL, but IF, it would be great.

Susan

Re: Selecting all records based on a specific value for a variable.

Flip — Thu, 26 Mar 2009 13:08:35 GMT

I think this was covered as well in the earlier discussion.

http://support.sas.com/forums/thread.jspa?threadID=5281&tstart=0

create table no_zeros as select a.* from source as a, (select a, f from source where b=0) as b
where not(a.a = b.a and b.f = a.f);

(I didn't run this so I hope I didn not drop in any syntax errors)

Re: Selecting all records based on a specific value for a variable.

deleted_user — Thu, 26 Mar 2009 13:11:40 GMT

Sorry, I meant still to keep those where there was an amount greater than 0.

But now I would like to keep the ones where the combination of ID and F is the same as it was where that amount could be found.

So

2 A 0
2 A 1
2 B 0
4 B 1
4 A 1
4 A 0
4 A 1
6 B 0
6 A 1

would become

2 A 0
2 A 1
DELETE
4 B 1
4 A 1
4 A 0
4 A 1
DELETE
6 A 1

Susan

Re: Selecting all records based on a specific value for a variable.

Flip — Thu, 26 Mar 2009 13:12:46 GMT

create table no_zeros as select a.* from source as a, (select a, f from source where b>0) as b
where (a.a = b.a and b.f = a.f);

Re: Selecting all records based on a specific value for a variable.

deleted_user — Thu, 26 Mar 2009 13:46:22 GMT

Flip

I am trying to translate your code. My variables are ID, F and AMOUNT. I happened to use A and B as two values of F, and your a and b makes me confused.

I am taking the easy way and ask you, instead of trying to solve it myself.

Time ...

Susan

Re: Selecting all records based on a specific value for a variable.

sbb — Thu, 26 Mar 2009 13:58:58 GMT

It appears that on this forum, the factor "time" is far too often a challenge, thereby compromising one's opportunity to learn through DOC reading, digestion, code-composition, self-diagnosis and interpretation. How unfortunate.

Scott Barry
SBBWorks, Inc.

Re: Selecting all records based on a specific value for a variable.

Flip — Thu, 26 Mar 2009 14:12:03 GMT

In SQL
table before the dot variable after
dataset alias . variable

or
table.field

create table no_zeros as select a.* from source as a, (select amount, f from source where b>0) as b
where (a.amount = b.amount and b.f = a.f);

Re: Selecting all records based on a specific value for a variable.

deleted_user — Fri, 27 Mar 2009 07:23:18 GMT

I agree with mr Barry that it's unfortunate.

Susan

Re: Selecting all records based on a specific value for a variable.

deleted_user — Fri, 27 Mar 2009 13:39:37 GMT

Flip

Thanks.

My programming is running now.

And it keeps on running. For more than 5 minutes now.

My table has more than 500 000 records. Isn't PROC SQL appropriate with so many records?

Susan

Re: Selecting all records based on a specific value for a variable.

Flip — Fri, 27 Mar 2009 14:07:20 GMT

A sub querry may at times be inefficient.
You could try creating a work table of those IDs to keep (or delete) and do an outer join.