edit check query

[rakeshvvv]

I Have to run to edit check query on table having collumns customerid, product and orderno.

ordernumber has values(1-5). not all ordernumbers(1-5) are valid for every product.If the same product has all the values from 1-5 , then it is discrepancy. I need to pull out those records with discrepany.

ID   product orderno

001 apple      1

001 apple      2

001 apple      3

001 apple      4

001 apple      5

002 orange    1

002 orange    3

003 banana   1

003 banana   2

004 berry      1

004 berry       2

In the above table id(001) with product apple is discrepany because it has all the values 1-5. i need to pull out those reocord from the table.

Thansk in advance

Rakesh

[RichardinOz]

Something like this (not tested)

Proc sql ;

     create table want as

          select a.*

          from have a

               , (Select product

                    ,  count (distinct order) as tot

                    from have

                 )     b

          where a.product = b.product

               and b.tot = 5

     ;

quit ;

Richard

[rakeshvvv]

Thanks Richard......