PROC SQL not equal to operator

[SASAna]

Hi SAS users,

IS there "Not equal" operator in PROC SQL?

I am trying to do the below operation to get the cust1, cust2 only when there is 3 level merge.

proc sql;
create table out as
select  a.*
    ,b.Cust1
    ,b.Cust2
from c01 a , c09 b
where a.clm = b.clm
  and a.DRG = b.DRG
  and a.TIN = b.TIN
union
select  a.*
    from c01 a , c09 b
where a.clm = b.clm
and a.DRG <> b.DRG
  and a.TIN <> b.TIN
;
quit;

Thanks,

Ana

[Kurt_Bremser]

Use the NE mnemonic.

[HB]

Don't see the purpose of the union.  Explain more.

Convert to standard SQL.

data a;
input my1:$2. my2:$2. clm drg tin;
datalines;
aa bb 1 2 3
bb cc 2 3 4
dd ee 3 4 5 
ff gg 4 5 6
hh ii 6 7 8
;
run;

data b;
input cust1:$2. cust2:$2. clm drg tin;
datalines;
aa bb 9 2 3
bb cc 8 3 4
zz xx 3 4 5 
yy ww 4 5 6
hh ii 2 7 8
;
run;


proc sql;
	create table out as
	select  a.*,
    b.Cust1,
    b.Cust2
	from a a inner join b b
	on a.clm = b.clm and a.DRG = b.DRG and a.TIN = b.TIN;
quit;

Yields

The SAS System
my1	my2	clm	drg	tin	cust1	cust2
dd	ee	3	4	5	zz	xx
ff	gg	4	5	6	yy	ww

[ballardw]

You really should provide short examples, may be four rows of each of your two data sets and what you expect from the output.

You might find the not equal results in many more records in your output than you expect. The following code is just show a small example using ne in comparisons:

data work.co1;
   input row clm drg tin;
datalines;
1 1 1 1
2 1 1 2
3 1 1 3
4 1 2 4
5 1 2 5
;
run;

data work.co9;
   input row clm drg tin;
   cust1=rand('uniform');
   cust2=rand('uniform');

datalines;
11 1 1 1
12 1 1 2
13 1 1 7
14 1 2 8
15 1 2 9
;
run;

proc sql;
   create table example as
   select a.* ,b.row as brow
   from work.co1 as a ,
        work.co9 as b
   where a.clm = b.clm
      and a.DRG ne b.DRG
      and a.TIN ne b.TIN
  ;
quit; 

[mkeintz · Accepted solution]

In SAS, you can use the NE mnemonic for "not equal to" -- but...

You apparently want all joins within a CLM in which either BOTH drg and tin match or neither match.  I.e. you don't want instances in which only one of them matches.  I don't think you need a union operation for this.

Instead just compare the result of a.drg=b.drg to the result of a.tin=b.tin:

proc sql;
  create table out as
    select a.*, b.cust1,b.cust2
    from co1 as a, co9 as b
    where a.clm=b.clm   and (a.drg=b.drg)=(a.tin=b.tin);
quit;

The logical expressions "a.drg=b.drg" and "a.tin=b.tin" will each return a 1 or 0, depending on whether the respective equality comparison is true.  So you'll keep only those with "1=1"  (both compare equal) or "0=0" (neither equal).