Solved

# SAS-Query for nonmatch records

Posted on 2012-03-30
277 Views
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
Question by:sam2929
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 2
• 2

LVL 11

Accepted Solution

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;
``````

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

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

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

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

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;
``````

or is this not getting what you want either?
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

### Suggested Solutions

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
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…
###### Suggested Courses
Course of the Month9 days, 1 hour left to enroll