BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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
13 REPLIES 13
deleted_user
Not applicable
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;
deleted_user
Not applicable
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;
deleted_user
Not applicable
Yup, I agree - having mis-read the original question! :@(
deleted_user
Not applicable
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
Flip
Fluorite | Level 6
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)
deleted_user
Not applicable
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
Flip
Fluorite | Level 6
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);
deleted_user
Not applicable
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
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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.
deleted_user
Not applicable
I agree with mr Barry that it's unfortunate.

Susan
Flip
Fluorite | Level 6
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);
deleted_user
Not applicable
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
Flip
Fluorite | Level 6
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.

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 13 replies
  • 853 views
  • 0 likes
  • 3 in conversation