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

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.

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
  • 7 replies
  • 3143 views
  • 1 like
  • 4 in conversation