topic Re: PROC SQL not equal to operator in SAS Programming

PROC SQL not equal to operator

SASAna — Tue, 05 Dec 2017 18:43:24 GMT

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

Re: PROC SQL not equal to operator

Kurt_Bremser — Tue, 05 Dec 2017 19:41:27 GMT

Use the NE mnemonic.

Re: PROC SQL not equal to operator

HB — Tue, 05 Dec 2017 19:47:11 GMT

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

Re: PROC SQL not equal to operator

ballardw — Tue, 05 Dec 2017 20:15:14 GMT

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;

Re: PROC SQL not equal to operator

mkeintz — Thu, 20 Sep 2018 13:50:47 GMT

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