Solved

SAS-Query for nonmatch records

Posted on 2012-03-30
5
277 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
[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
  • Learn & ask questions
  • 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

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…
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…

738 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