• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 293
  • Last Modified:

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
0
sam2929
Asked:
sam2929
  • 2
  • 2
1 Solution
 
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
 
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now