Query missing values; creating another column using two existing ones 1

[DonaZ]

I’m having a problem with missing values and columns.
I want to create another column using the two existing columns – ed1 and ed2 to create ed3.
This following does not work. Should I use . to represent a missing value? Can I create another column this way? What am I doing wrong? Thank you for your assistance.

Proc sql;

Select id, ed1, ed2,
Case
When ed1 is null then ed2
When ed2 is null then ed1
When ed1 >= ed2 then ed1
When ed2 > ed1 then ed2
Else null
End as ed3;
Quit;

 

[dblan]

DonaZ,
Are you getting the error message "The following columns were not found in the contributing tables: null." ?

If so, try to run it with the "else null" removed from the code. So it looks like:

Proc sql;
create table <tablename1> as
Select id, ed1, ed2,
Case
When ed1 is null then ed2
When ed2 is null then ed1
When ed1 >= ed2 then ed1
When ed2 > ed1 then ed2
End as ed3
from <tablename2>;
Quit;

This is how I get it around it.
You'll get a warning message in the log that says something like "unaccounted CASES will result in a missing value..." , but it will still create the table and insert nulls for the missing values (which is what you want).

 

[DonaZ]

dblan,

I used your query and it still didn't work. Below is the error messsage.

A CASE expression has no ELSE clause. Cases not accounted for by the WHEN clauses will result in a missing value for the CASE expression.

What does this mean? Any suggestions?

Thank you.

donaz

 

[dblan]

Hmmm, are you sure the table wasn't created?

I just ran a test and while I got the same message, it created the table.

NOTE: WORK.ED was successfully created.
4295 Proc sql;
4296 create table work.ed2 as
4297 Select id, ed1, ed2,
4298 Case
4299 When ed1 is null then ed2
4300 When ed2 is null then ed1
4301 When ed1 >= ed2 then ed1
4302 When ed2 > ed1 then ed2
4303 End as ed3
4304 from work.ed;
NOTE: A CASE expression has no ELSE clause. Cases not accounted for by the WHEN clauses will result
in a missing value for the CASE expression.
NOTE: Table WORK.ED2 created, with 4 rows and 4 columns. <-------

About the only other thing I can think of is in the else use '' (no spaces between single quotes).

...When ed2 > ed1 then ed2 else '' end as ed3 ...

Hope this helps.

 

[DonaZ]

Thank you very much dblan. I found my error.
Have a good day!