-1

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;

fruits Indicator
apple A
Strawberry B
apple B
Strawberry B
Orange A
Orange B
Mango B
Banana A
Peach B
Cherry A
Strawberry B

Output that I want:

fruits Indicator
Mango B
Peach B
strawberry B

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:
fruits Indicator
apple B
orange B
mango B
peach B
strawberry B 
I need to add another step ..i.e. if fruit = both A and B indicator then do not get that value.
kfc123456
  • 1
  • 1
  • 3
    For some tips on how you could improve your question see [How to ask](https://stackoverflow.com/help/how-to-ask), [tips-for-asking-a-good-structured-query-language-question](https://meta.stackoverflow.com/questions/271055) and how to provide a [Minimal, Reproducible Example](https://stackoverflow.com/help/minimal-reproducible-example) and how not to use [pictures](https://meta.stackoverflow.com/questions/285551) of data, code or errors – Stu Dec 02 '22 at 23:44
  • 1
    As per the question guide, please do not post images of code, data, error messages, etc. - copy or type the text into the question. Please reserve the use of images for diagrams or demonstrating rendering bugs, things that are impossible to describe accurately via text. – Dale K Dec 03 '22 at 00:32

2 Answers2

0

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
;

enter image description here

Tom
  • 47,574
  • 2
  • 16
  • 29
  • Can you explain how max( sex='M') works? What is the logic behind that line? – Negdo Dec 05 '22 at 07:55
  • 1
    Boolean expressions evaluate to 1 (true) or 0 (false). The MAX() aggregate function will result in 1 (true) when ANY of the observations are true. The MIN() aggregate function will result in 1 (true) when ALL of the observations are true. – Tom Dec 05 '22 at 13:51
  • Is this feature of MAX() function described in any documentation? Because this: https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/Functions/Aggregate/MAXAggregate.htm is the only thing I found. So I can find out if there are other "hidden" features of common functions – Negdo Dec 05 '22 at 14:31
  • 1
    Not a "feature". It is a consequence of taking the max over a series of boolean values. – Tom Dec 05 '22 at 16:13
0

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;

Manoharan
  • 11
  • 1