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
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
Thanks Richard......
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.