proc sql coalesce

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 121
Accepted Solution

proc sql coalesce

[ Edited ]

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":

recoded.png

 

Error:

error.png


Accepted Solutions
Solution
‎04-26-2017 06:49 AM
Frequent Contributor
Posts: 121

Re: proc sql coalesce

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;

View solution in original post


All Replies
Esteemed Advisor
Posts: 5,198

Re: proc sql coalesce

I don't see the log, just the paper attached.
Data never sleeps
Grand Advisor
Posts: 17,396

Re: proc sql coalesce

[ Edited ]

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. 

Frequent Contributor
Posts: 121

Re: proc sql coalesce

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

Re: proc sql coalesce

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;
Grand Advisor
Posts: 17,396

Re: proc sql coalesce


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. 

 

 

 

Solution
‎04-26-2017 06:49 AM
Frequent Contributor
Posts: 121

Re: proc sql coalesce

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;

Esteemed Advisor
Posts: 5,198

Re: proc sql coalesce

Oh now I see.
SAS doesn't use three level names. You must assign aliases to your table names.
Data never sleeps
Frequent Contributor
Posts: 121

Re: proc sql coalesce

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

Re: proc sql coalesce

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 164 views
  • 4 likes
  • 3 in conversation