topic Re: Merge with a dataset and create a new variable if a value of an existing variable is missing in SAS Programming

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

gsk — Thu, 31 Oct 2019 13:40:21 GMT

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.

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

PaigeMiller — Thu, 31 Oct 2019 14:22:33 GMT

@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;

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

s_lassen — Thu, 31 Oct 2019 14:36:10 GMT

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!

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

Kurt_Bremser — Thu, 31 Oct 2019 16:56:16 GMT

I guess you wanted to do this:

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