coalesce VS union in proc sql

[azertyuiop]

Hello , Good Morning ,

In SAS if I use a proc sql with a UNION like this :

create table mytable as

select field1 , field2 , field3
from mystarttable
where cats(field1,'-',field2) = /* condition */
and field2 is not null
union
select field1 , field2 , field3
from mystarttable
where cats(field1) = /* condition */
and field2 is null ;

And after I decide to use "coalesce" function :

create table mytable as

select field1 , field2 , field3
from mystarttable
where cats(field1,'-',coalesce(field2,' ') )= /* condition */ ;

For you the result it's the same or it's different ?

[Kurt_Bremser · Accepted solution]

Try it.

[azertyuiop]

I'have tried this query under SAS in the present case it's not the same result ...

In my departement of company a collabor say :

"If the field B is empty for several and not empty for other value you can use this , it's to replace the union".

I consider this opinion like false ...

I found a different result ...

[Kurt_Bremser]

---

@azertyuiop wrote:

............

I found a different result ...

---

See?

Never underestimate the "experiment" part of the scientific method.

[azertyuiop]

For example a set of data to test :

field1;field2;field3
01;10;ID123456
01;11;ID123456
02;;ID123456
02;;ID123457
02;11;ID123457
02;11;ID123458
09;19;ID123459
09;;ID123459

[ballardw]

---

@azertyuiop wrote:

For example a set of data to test :

field1;field2;field3
01;10;ID123456
01;11;ID123456
02;;ID123456
02;;ID123457
02;11;ID123457
02;11;ID123458
09;19;ID123459
09;;ID123459

---

Post example data as a data step. A lot of us are not going to write one to read example data because we may not create a data set that is the same. For instance we do not know if your actual field1 or field2 are character or numeric.

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

[RW9]

What is the question?  All I can see is you have added this:

coalesce(field2,' ') 

Which is saying if field is present use this, but if it doesn't contain anything, use nothing.  I.e. if field2="" then "" else field.

Adding this part makes no sense.  Remember in SAS a variable has a length, and the length is a set of boxes, '  ' doesn't mean anythiing. 

[azertyuiop]

In my case field2 can be empty or not empty in the same column .

For exemple at line 1 "field2" is empty and not empty in line 2 .

It's to separate two case of selection in program .