How to get distinct fruits where indicator =only B and make sure that fruit listed is not coded on any "A" Indicator column.

I tried this but obviously its not working. EDITED:

example;

Output that I want:

Note: even though Apple and Orange has A and B, we do not want those on the output since both also have indicator A. We want fruits that only has B.

Code that I used: proc sql; create table unique as select distinct fruits, indicator from example where indicator='b' and fruits in(select distinct fruits from example where indicator='b'); quit;

but this gets:

I need to add another step ..i.e. if fruit = both A and B indicator then do not get that value.  

CodePudding user response：

It is not clear what you criteria is.

If the goal is to select all of the observations that only have 'B' in INDICATOR then use an aggregate function in the having clause.

select *
 from have
 group by fruits
 having min( indicator='B' ) = 1
;

Try these examples:

select age
     , count(*) as count
     , max( sex='M') as any_male
     , min( sex='M') as all_male
     , max( sex='F') as any_female
     , min( sex='F') as all_female
 from sashelp.class
 group by age 
;

select * from sashelp.class
group by age
having min( sex='M' ) = 1
;

CodePudding user response：

please refer below code for you answers

PROC SQL; CREATE TABLE INPUTDS1 AS SELECT DISTINCT FRUITS,INDICATOR FROM INPUTDS WHERE INDICATOR = 'B' AND FRUITS NOT IN (SELECT DISTINCT FRUITS FROM INPUTDS WHERE INDICATOR = 'A') ; RUN;