Help using Base SAS procedures

coalesce VS union in proc sql

Accepted Solution Solved
Reply
Contributor
Posts: 68
Accepted Solution

coalesce VS union in proc sql

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 ?

 

 

 


Accepted Solutions
Solution
‎11-20-2017 05:04 AM
Super User
Posts: 10,280

Re: coalesce VS union in proc sql

Posted in reply to azertyuiop

Try it.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

View solution in original post


All Replies
Solution
‎11-20-2017 05:04 AM
Super User
Posts: 10,280

Re: coalesce VS union in proc sql

Posted in reply to azertyuiop

Try it.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 68

Re: coalesce VS union in proc sql

Posted in reply to KurtBremser

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

 

Super User
Posts: 10,280

Re: coalesce VS union in proc sql

Posted in reply to azertyuiop

azertyuiop wrote:

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

 

I found a different result ...

 


See?

 

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 68

Re: coalesce VS union in proc sql

Posted in reply to KurtBremser

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

Super User
Posts: 13,583

Re: coalesce VS union in proc sql

Posted in reply to azertyuiop

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.

Super User
Super User
Posts: 9,599

Re: coalesce VS union in proc sql

Posted in reply to azertyuiop

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. 

Contributor
Posts: 68

Re: coalesce VS union in proc sql

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 .

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 414 views
  • 1 like
  • 4 in conversation