topic Re: How to subset data based on a list of values in SAS Programming

How to subset data based on a list of values

msyteriouspages — Sat, 11 Sep 2021 10:18:35 GMT

I have a huge dataset that I want to subset based on values that already exist in another dataset. For example:

Dataset A

ID Dollars
1 100
2 135

Dataset B

Key Age App Funds
1 23 1 1000
1 23 2 1500
2 22 1 6700
2 22 2 2000
3 26 1 3100
3 26 2 2300

I wanted to create a list (maybe a macro list would be suitable?) based on the ID values from Dataset A, e.g.
IDlist = 1,2

Then, I would like to subset Dataset B based on ID’s that exist in our previously created IDlist. And I would use this in a proc sql (or data step) like:

proc sql:
create table want as
Select *
From B
Where key in IDlist;
quit;

The ideal subsetted dataset B should look like:

Data want:

Key Age App Funds
1 23 1 1000
1 23 2 1500
2 22 1 6700
2 22 2 2000

If anyone has dealt with something like this before, I’d appreciate any input. Thank you!

Re: How to subset data based on a list of values

tarheel13 — Sat, 11 Sep 2021 10:47:00 GMT

People are going to get mad at you if you don’t paste the datasets as datalines.

Re: How to subset data based on a list of values

msyteriouspages — Sat, 11 Sep 2021 10:50:15 GMT

Hi, sorry, I’m new to SAS communities. Any tips on how I could paste them as datalines?

Re: How to subset data based on a list of values

tarheel13 — Sat, 11 Sep 2021 11:02:37 GMT

Data A;
input ID dollars;
datalines;
1 100
2 135
;
run;

Like that. Also, if you are posting code, please use the insert SAS code button.

Re: How to subset data based on a list of values

Ksharp — Sat, 11 Sep 2021 11:19:35 GMT

proc sql:
create table want as
Select *
From B
Where key in (select id from A) ;
quit;