topic Re: coalesce VS union in proc sql in SAS Procedures

coalesce VS union in proc sql

azertyuiop — Fri, 17 Nov 2017 10:39:15 GMT

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 ?

Re: coalesce VS union in proc sql

Kurt_Bremser — Fri, 17 Nov 2017 10:45:39 GMT

Try it.

Re: coalesce VS union in proc sql

RW9 — Fri, 17 Nov 2017 10:49:17 GMT

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.

Re: coalesce VS union in proc sql

azertyuiop — Fri, 17 Nov 2017 11:12:08 GMT

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

Re: coalesce VS union in proc sql

Kurt_Bremser — Fri, 17 Nov 2017 11:15:10 GMT

@azertyuiop wrote:

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

I found a different result ...

See?

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

Re: coalesce VS union in proc sql

azertyuiop — Fri, 17 Nov 2017 11:16:07 GMT

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 .

Re: coalesce VS union in proc sql

azertyuiop — Fri, 17 Nov 2017 14:04:26 GMT

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

Re: coalesce VS union in proc sql

ballardw — Fri, 17 Nov 2017 16:30:22 GMT

@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-data-AKA-generate/ta-p/258712 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.