Solved

SAS-Query for nonmatch records

Posted on 2012-03-30
5
238 Views
Last Modified: 2012-04-20
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
Comment
Question by:sam2929
  • 2
  • 2
5 Comments
 
LVL 11

Accepted Solution

by:
theartfuldazzler earned 500 total points
ID: 37787468
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
 

Author Comment

by:sam2929
ID: 37788532
thts exactly what i want can't we concat item_no||code make it unique and
do <> then in sql
0
 
LVL 11

Expert Comment

by:theartfuldazzler
ID: 37792439
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
 

Author Comment

by:sam2929
ID: 37793142
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
 
LVL 14

Expert Comment

by:Aloysius Low
ID: 37799217
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
Read about achieving the basic levels of HRIS security in the workplace.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now