DATA Step, Macro, Functions and more

PROC SQL not equal to operator

Reply
Frequent Contributor
Posts: 124

PROC SQL not equal to operator

 

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

Super User
Posts: 9,860

Re: PROC SQL not equal to operator

Use the NE mnemonic.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super Contributor
Super Contributor
Posts: 266

Re: PROC SQL not equal to operator

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
Super User
Posts: 13,283

Re: PROC SQL not equal to operator

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; 
Trusted Advisor
Posts: 1,309

Re: PROC SQL not equal to operator

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).

 

Ask a Question
Discussion stats
  • 4 replies
  • 1498 views
  • 2 likes
  • 5 in conversation