[SOLVED] SAS proc sql: check for each value if value is in a list (with different list for each value)

SAS proc sql: check for each value if value is in a list (with different list for each value)

Suppose that we have a group of people and all those people have a bag of clothes. There is a table with for each person a record per item in their bag. EX, Maria has a bag with a hat, some pants and shoes. Luka has a bag with pants, a shirt, socks and a sweater. John has a bag with socks and a hat. Kristen has a bag with earrings, shoes and a sweater.

Table: BAGS

Name	item
Maria	hat
Maria	pants
Maria	shoes
Luke	pants
Luke	shirt
Luke	socks
Luke	sweater
John	socks
John	hat
Kristen	earrings
Kristen	shoes
Kristen	sweater

Now suppose that there is another table with expected items. EX Maria was supposed to bring a shirt and a hat. Luka was supposed to bring socks and a sweater. John is supposed to bring pants and Kirsten is supposed to bring shoes.

Table: Asked

Name	item	Brought
Maria	shirt	n
Maria	hat	y
Luke	socks	y
Luke	sweater	y
John	pants	n
Kristen	shoes	y

For every record in the asked table, I want to know wether this person has the item in the bag or not (see third column). So, check if the combination of the name and item in table 'ASKED' is included in the table 'NAMES'. I don't know how to do this, with a where statement it does not work because of the list to check is different depending on the name.

what I tried:

proc sql;
create table brought as (
select name
    , item
    , case when (name, item) in (select (name, item) from BAGS)
        then y
        else n end 
from ASKED
)
;quit;

This does not work, how can i do this kind of thing?

Solution

I could identify some syntax issues, in your code -> this code should correct it:

proc sql;
create table brought as 
select a.name, 
       a.item, 
       case when b.name is not null and b.item is not null then 'y' else 'n' end as Brought
from Asked a
left join Bags b
on a.name = b.name and a.item = b.item
;quit;