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
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).
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 |
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;
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).
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.