Subset by group that meets two conditions

[SanKH1]

I have this dataset

data test;

input store$ type1 type2;

datalines;

1 1 0
1 0 1
2 0 0
2 0 1
3 1 0
3 0 0;

run;

We need to only filter stores that meet both type1=1 and type2=1. In this case, only store 1 satisfies this condition. 

[PaigeMiller]

We need to only filter stores that meet both type1=1 and type2=1. In this case, only store 1 satisfies this condition.

I don't see any such store satisfying the condition in your data.

[SanKH1]

Sorry, I should've explain more. From the same store, first observation: type1 needs to be 1 and second observation: type2 needs to be 1.

[PaigeMiller]

proc summary data=have nway;
    class store;
    var type1 type2;
    output out=intermediate sum=;
run;
data want;
    set intermediate;
    where type1=1 and type2=1;
run;

[SanKH1]

Thanks, it works. But to simplify things, I didn't include other variables in the datalines that are part of the dataset. Is there a way to keep all columns?

[PaigeMiller]

---

@SanKH1 wrote:
Thanks, it works. But to simplify things, I didn't include other variables in the datalines that are part of the dataset. Is there a way to keep all columns?

---

It is a good idea to write a thorough problem description. Then we don't have to do "I don't understand"—"Oh thank you for the explanation" — "Here's code that works" — "No it doesn't work for the real problem" dance. Yes of course, there are ways to keep all columns, and I see that @PGStats has provided an alternative, so I won't bother.

[SanKH1]

Apologies and noted.

[PGStats · Accepted solution]

If you prefer to use SQL:

proc sql;
create table want as
select * from test
group by store
having max(type1)=1 and max(type2)=1;
quit;

[SanKH1]

Thanks! Just one question what does max do in this case if you are already saying type1=1?

[PGStats]

Max(type1)=1 requests stores for which the maximum value of type1 is one, so that it is equal to one for at least one observation.

Note: the code assumes that type1 and type2 only take values 0 or 1.

[SanKH1]

Thank you!

[SanKH1]

I have a follow-up question. Is it possible to create a variable (goal) following the statement "having max(type1)=1 and max(type2)=1" that will be goal=1. Something like if (having statement) then goal=1. I am aware there will only be ones for this newly created variable. But we need this variable so we can then merge with other datasets and then filter rows what will be goal=1.

I did this and it worked, but is this the right way?

proc sql;
create table want as
select * , 1 as goal from test
group by store
having max(type1)=1 and max(type2)=1;
quit;