SAS-Query for nonmatch records

i have two table

fact
item_no  code
10001     ABC
10001    BCD
10002    BCD
10002    EFG



STAGE
item_no  code
10001     ABC
10001    BCD
10002    FGH
10002    EFG

I want result like

FCT
10002    BCD

SO i am runing below query which is not giving me desire result


select a.item_no
from fact a,stage b
WHERE
a.ITEM_NO = b.ITEM_NO
and a.CODE <> bCODE
sam2929Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

theartfuldazzlerCommented:
Hi Sam

SQl runs a Cartesian Product, and then looks at the where clause.

In other words, it takes  the first record of "fact":
10001     ABC

and matches it to all 4 records in "Stage"
10001     ABC
10001    BCD
10002    FGH
10002    EFG

the end result is:
10001     ABC  | 10001     ABC | where clause is false
10001    BCD  | 10001     ABC | where clause is true
10002    FGH  | 10001     ABC | where clause is false
10002    EFG  | 10001     ABC | where clause is false

It repeats this for all four records of "fact"


-------------------------

I assume you want to check record 1 of "fact" vs record 1 of "Stage" (and not against record 2,3,& 4 of "stage")

The easiest way I can think to do this, is using two SET statements in a DATA step:
DATA Fact;
informat item_no 5. code $3.;
INPUT item_no code;
CARDS;
10001     ABC
10001    BCD
10002    BCD
10002    EFG
;
RUN;


DATA Stage;
informat item_no 5. code $3.;
INPUT item_no code;
CARDS;
10001     ABC
10001    BCD
10002    FGH
10002    EFG
;
RUN;

DATA FCT;
  SET Stage;
    Stage_no = item_no;
	Stage_code = code;

  SET Fact;

  IF Item_no = Stage_no and Stage_code ^= code then output;

  KEEP Item_no Code;
RUN;

Open in new window


The problem is this always compares record 1 to record1 only, and Record2 to Record2 only...  Not sure what you are trying to acheive otherwise?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sam2929Author Commented:
thts exactly what i want can't we concat item_no||code make it unique and
do <> then in sql
0
theartfuldazzlerCommented:
Hi

One could do that I suppose, but I would think it more efficient to do a SQL EXCEPT statement:

PROC SQL;
Select item_no, code from stage
Except
Select item_no, code from fact;
0
sam2929Author Commented:
Except will not work
its bit different what i explained earlier

fact
item_no  code
10001     ABC
10001    BCD
10002    BCD
10002    EFG
10002    FGH




STAGE
item_no  code
10002    FGH
10002    EFG

I want result like

FCT
10002    BCD

as stage item_no 10002 don't have that code so i want to select that item_no and the
code from fact which is not in stage
0
Aloysius LowCommented:
could you do a single-sided join on the fact table with the stage table by matching both item_no and code and bringing in the stage table's item_no (as item_no_stage) and code (as code_stage) then perform an additional step to filter out the records where item_no_stage and code_stage are blank?
proc sql;
	create table fct as
		select item_no_fact as item_no,
			code_fact as code
		from (
			select fact.item_no as item_no_fact,
				fact.code as code_fact,
				stage.item_no as item_no_stage,
				stage.code as code_stage
			from fact
			left join stage
			on fact.item_no = stage.item_no and
				fact.code = stage.code
		)
		where item_no_stage = . and code_stage = '';
quit;

Open in new window


or is this not getting what you want either?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.