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.
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;
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.
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;
@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.
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;
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.
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;
Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.