BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Cruise
Ammonite | Level 13

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

1 ACCEPTED SOLUTION

Accepted Solutions
Cruise
Ammonite | Level 13

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

9 REPLIES 9
LinusH
Tourmaline | Level 20
I don't see the log, just the paper attached.
Data never sleeps
Reeza
Super User

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
Ammonite | Level 13
@Reeza, I just edited the code, but now it appears to be a long continuous line after editing.
Cruise
Ammonite | Level 13
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
Super User

@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
Ammonite | Level 13

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
Tourmaline | Level 20
Oh now I see.
SAS doesn't use three level names. You must assign aliases to your table names.
Data never sleeps
Cruise
Ammonite | Level 13
@LinusH, I'm a beginner in sql, could you please elaborate on three level names and assigning aliases to table names? thanks
LinusH
Tourmaline | Level 20

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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