|
The COALESCE() function really doesn't have the same function in a data step
that it does in SQL. In SQL
the COALESCE() solves a particular problem. It allows a program either to
assign a value from an
update table to x if an ID in the update table joins to an ID in a primary
table, or to fill in with a x from the
primary table if not. For example,
SELECT COALESCE(t2.x,t1.x) AS x FROM t1 AS t1 LEFT JOIN t2 AS t2 on
t1.ID=t2.ID;
In this case, simply selecting t2.x would leave the value x missing where t2.ID
does not match any t1.ID. The
COALESCE() substitutes the value of t1.x for t2.x where t.x is missing.
The IN=xx option on a SAS MERGE and datastep statement IF xx THEN x=z2 ELSE x=z1
works in a data step
much the same as COALESCE() works in SQL where you want the value of z2 if the
group identified by the
automatic variable xx exists, but z1 if not. To make this work, you will have
to give corresponding variables
in different datasets different names.
I am of course assuming that you are asking how to use COALESCE() for its
primary purpose. You could also use
to replace a missing value anywhere in SQL. For example,
data test;
y=.;
u=1;
run;
proc sql;
select coalesce(y,u) as x from test;
quit;
yields x=1. Sig
____________________Reply Separator____________________
Subject: Datastep equivalent of SQL function COALESCE
Author: Bart Heinsius <Bart.Heinsius@EOMDATA.NL>
Date: 11/4/99 4:52 PM
Hi,
Does anyone know of a datastep equivalent of the SQL function COALESCE?
From the SAS help:
The COALESCE function returns the first argument whose value is not a SAS
missing value.
Cheers,
Bart Heinsius
E.O.M. Data
Hilversum, the Netherlands
http://www.eomdata.nl
| 
