BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
SASAna
Quartz | Level 8

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
mkeintz
PROC Star

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

 

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

View solution in original post

4 REPLIES 4
HB
Barite | Level 11 HB
Barite | Level 11

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
Super User

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
PROC Star

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

 

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 55073 views
  • 2 likes
  • 5 in conversation