BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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?
3 REPLIES 3
deleted_user
Not applicable
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
Not applicable
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
Not applicable
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

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!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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