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:
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;
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.
@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.
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;
It's all in the doc, see also this example:
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.