BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
ANKH1
Pyrite | Level 9

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. 

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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;
PG

View solution in original post

11 REPLIES 11
PaigeMiller
Diamond | Level 26

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.

--
Paige Miller
ANKH1
Pyrite | Level 9
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
Diamond | Level 26
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;
--
Paige Miller
ANKH1
Pyrite | Level 9
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
Diamond | Level 26

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

--
Paige Miller
ANKH1
Pyrite | Level 9
Apologies and noted.
PGStats
Opal | Level 21

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;
PG
ANKH1
Pyrite | Level 9
Thanks! Just one question what does max do in this case if you are already saying type1=1?
PGStats
Opal | Level 21

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.

 

PG
ANKH1
Pyrite | Level 9
Thank you!
ANKH1
Pyrite | Level 9

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;

 

SAS Innovate 2025: Register Now

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!

What is Bayesian Analysis?

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 11 replies
  • 1492 views
  • 1 like
  • 3 in conversation