proc sql coalesce

[Cruise]

I'd like merge "recoded" data with recoded identifier variable ("new_obs_num") back onto the original "mydata". Original dataset "mydata"  is a cluster of duplicate patients that "new_obs_num" should now serve as a common identifier field. I need to uniquely identify patients.

Below code didn't work, see error attached. It is modified code from the attached reference, page 12.

proc sql;
create table encoded as
select coalesce(recoded1.new_obs_num,original.id) as dedup_id, original.*
from sel.ordata as original LEFT JOIN sel.recoded1
on original.id = sel.recoded1.new_obs_num
;
quit;

 What am I doing wrong?

Data "recoded":

Error:

[LinusH]

I don't see the log, just the paper attached.

[Reeza]

Your log doesn't match the code posted. 

ok. You fixed it to match error. It was probably correct before - the previous code posted looked correct. 

[Cruise]

@Reeza, I just edited the code, but now it appears to be a long continuous line after editing.

[Cruise]

this is previous code where i simply omitted libnames for more appealing look in the post. it didn't work either:
proc sql;
create table encoded as
select coalesce(recoded1.new_obs_num,original.id) as dedup_id, original.*
from sel.ordata as original LEFT JOIN sel.recoded1
on original.id = sel.recoded1.new_obs_num
;
quit;

[Reeza]

---

@SUNY_Maggie wrote:
this is previous code where i simply omitted libnames for more appealing look in the post. it didn't work either:
proc sql;
create table encoded as
select coalesce(recoded1.new_obs_num,original.id) as dedup_id, original.*
from sel.ordata as original LEFT JOIN sel.recoded1 as WRONG
on original.id = WRONG.new_obs_num
;
quit;

---

You used an alias for the other table - as original. 

[Cruise · Accepted solution]

I tried all your suggestions on the small sample data and below is how it worked out. THANKS.

data have;
input cat dog ant;
datalines;
1 2 3
2 5 6
2 8 0
1 2 3  
;
run;

data recode;
input new_obs_num old_obs_num;
datalines;
4 4
4 1
;
run;

data have; set have;
idvar=_n_;
run;

/*worked*/;

proc sql;
create table encodedx as
select coalesce(recode.new_obs_num, original.idvar) as dedup_id, original.*
from have as original LEFT JOIN recode
on original.idvar = recode.old_obs_num
;
quit;

[LinusH]

Oh now I see.
SAS doesn't use three level names. You must assign aliases to your table names.

[Cruise]

@LinusH, I'm a beginner in sql, could you please elaborate on three level names and assigning aliases to table names? thanks

[LinusH]

It's all in the doc, see also this example:

http://support.sas.com/documentation/cdl/en/sqlproc/69822/HTML/default/viewer.htm#p0xbwe8mkbjx9zn1fk...