topic Re: Selecting rows that contains certains values stored in another table in SAS Enterprise Guide

Selecting rows that contains certains values stored in another table

deleted_user — Mon, 09 Oct 2006 20:27:37 GMT

Hi guys,

How can I select rows in a data set based on values stored in another data set?

Eg:
DATA PEOPLE contains:
row id name
1 100 marcos
2 101 maria

DATA TRANSACTIONS contains:
row id id_people value
1 10 100 39$
2 11 100 33$
3 12 101 300$
4 13 102 400$
5 14 100 8$
6 15 102 98$

I want to select all rows in TRANSACTIONS that have id_people listed on PEOPLE (In this case, rows 1, 2, 3 and 5). How can I do that the best way possible in SAS?

Re: Selecting rows that contains certains values stored in another table

deleted_user — Mon, 09 Oct 2006 22:18:40 GMT

The easies way if you are using Enterprise Guide is to do a 'filter and query' step that joins your two tables. When you create the join you will probably need to do a manual join to link the two tables on your ID.

Let me know if you need more information on how to do this.

Cheers

Re: Selecting rows that contains certains values stored in another table

deleted_user — Wed, 11 Oct 2006 13:57:21 GMT

Well, i want to store in a data called ERRORS all the other rows that not have id_people listed in PEOPLE. In this example, rows 4 and 6.
Can I do that using the DATA STEP?

Re: Selecting rows that contains certains values stored in another table

deleted_user — Wed, 11 Oct 2006 23:39:58 GMT

If you want to output to two data sets in the same step, the a data step is the best way to go.

Try the following code. Keeping in mind that you may need to prefix your tables with the appropriate library references.

data mytrans errors;
merge people (rename=(id=id_people)) (in=p)
transactions (in=t);
by id_people;
if p=1 and t=1 then
output mytrans;
else
output errors;
run;

If you can't find the data sets after running this have a look in your 'work' library.
Message was edited by: DW at Oct 11, 2006 7:40 PM