topic Re: proc sql coalesce in SAS Programming

proc sql coalesce

Cruise — Fri, 21 Apr 2017 04:19:44 GMT

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:

Re: proc sql coalesce

LinusH — Fri, 21 Apr 2017 04:16:59 GMT

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

Re: proc sql coalesce

Reeza — Fri, 21 Apr 2017 04:22:57 GMT

~~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.

Re: proc sql coalesce

LinusH — Fri, 21 Apr 2017 04:19:09 GMT

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

Re: proc sql coalesce

Cruise — Fri, 21 Apr 2017 04:21:57 GMT

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

Re: proc sql coalesce

Cruise — Fri, 21 Apr 2017 04:24:45 GMT

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

Re: proc sql coalesce

Cruise — Fri, 21 Apr 2017 04:27:24 GMT

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;

Re: proc sql coalesce

Reeza — Fri, 21 Apr 2017 04:31:46 GMT

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

Re: proc sql coalesce

LinusH — Fri, 21 Apr 2017 07:30:08 GMT

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

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

Re: proc sql coalesce

Cruise — Fri, 21 Apr 2017 16:26:09 GMT

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;