Merge with a dataset and create a new variable if a value of an existing variable is missing

[gsk]

In SAS can we do something like

proc sql; 

    select a.*, b.new_var

    from data1 as a

    left join data2 as b

      on a.subjid=b.subjd; 

quit; 

but b.new_var is created if an observation from a has missing(variable1). If missing(a.variable1), then read b.variable2, b.variable3, b.variable4 and create a new_var based on some direction involving variable2 and variable3 and variable4. 

[PaigeMiller · Accepted solution]

---

@gsk wrote:

In SAS can we do something like

but b.new_var is created if an observation from a has missing(variable1). If missing(a.variable1), then read b.variable2, b.variable3, b.variable4 and create a new_var based on some direction involving variable2 and variable3 and variable4. 

---

For example, something like this

proc sql; 
    select a.*, 
      case when missing(variable1) then b.variable2+b.variable3+b.variable4
            end else . as new_var,
    from data1 as a
    left join data2 as b
      on a.subjid=b.subjd; 
quit; 

[s_lassen]

Just use the coalesce function:

proc sql; 
    select data1.*, coalesce(data2.new_var,data2.var2*data2.var3,data2.var4)
    from data1 
    left join data2
      on data1.subjid=data2.subjd; 
quit; 

In the case shown, if new_var is missing, the step will use var2*var3, and if that is also missing, var4, in this case all from data2 (but you could also use variables from the data1 table).

And for God's sake: Stop using stupid and non-informative aliases like "a" and "b" in PROC SQL. It may save you a few keystrokes in the beginning, but in the long run it makes the code totally unreadable!

[Kurt_Bremser]

I guess you wanted to do this:

coalesce(data1.variable,data2.var2*data2.var3,data2.var4) as new_var