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......
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.