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

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 ?

 

 

 

1 ACCEPTED SOLUTION
7 REPLIES 7
azertyuiop
Quartz | Level 8

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

 

azertyuiop
Quartz | Level 8

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

@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
Diamond | Level 26 RW9
Diamond | Level 26

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
Quartz | Level 8

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 .

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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