topic Re: Subset by group that meets two conditions in SAS Procedures

Subset by group that meets two conditions

ANKH1 — Wed, 01 Mar 2023 17:52:14 GMT

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.

Re: Subset by group that meets two conditions

PaigeMiller — Wed, 01 Mar 2023 18:12:28 GMT

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.

Re: Subset by group that meets two conditions

ANKH1 — Wed, 01 Mar 2023 18:26:22 GMT

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.

Re: Subset by group that meets two conditions

PaigeMiller — Wed, 01 Mar 2023 18:34:29 GMT

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;

Re: Subset by group that meets two conditions

PGStats — Wed, 01 Mar 2023 18:46:20 GMT

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;

Re: Subset by group that meets two conditions

ANKH1 — Wed, 01 Mar 2023 18:50:30 GMT

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?

Re: Subset by group that meets two conditions

ANKH1 — Wed, 01 Mar 2023 18:55:37 GMT

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

Re: Subset by group that meets two conditions

PaigeMiller — Wed, 01 Mar 2023 18:59:37 GMT

@ANKH1 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.

Re: Subset by group that meets two conditions

ANKH1 — Wed, 01 Mar 2023 19:01:41 GMT

Apologies and noted.

Re: Subset by group that meets two conditions

PGStats — Wed, 01 Mar 2023 19:30:02 GMT

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.

Re: Subset by group that meets two conditions

ANKH1 — Wed, 01 Mar 2023 19:40:02 GMT

Thank you!

Re: Subset by group that meets two conditions

ANKH1 — Wed, 01 Mar 2023 22:10:29 GMT

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;