Selecting rows that contains certains values stored in another table

[deleted_user]

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?

[deleted_user]

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

[deleted_user]

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?

[deleted_user]

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