INNER JOIN ERROR

[bhca60]

I'm trying to do an inner join in the final step on pid but I'm getting an error (I posted the previous two data steps for reference):

/*DENOMINATOR - merge to get dx rank for the dx codes*/
/*ran successfully and the ddate column populates correctly per the formula*/
data denom_final;
length dx $100;
merge opipdata (in=ina) 
      out.diag;
by pid;
format ddate yymmdd10.;
if ina;
if ((dxpos = 1 and dx in &aaa) then condition = "AAA";
if ((dxpos = 1 and dx in &ccc)  then condition = "CCC";
if ((dxpos  = 1 and dx in &ppp then condition = "PPP";
if ((dxpos = 1 and dx in &ppp)  then condition = "OOO";
if ((dxpos = 1 and dx in &ddd) then condition = "DDD";
if ((dxpos = 1 and dx in &hhh)  then condition = "HHH";

/*op data will not have disc date so create variable for when dischdate is missing and replace with edate*/

if missing(input(dischdate,yymmdd10.))then ddate= input(edate,yymmdd10.);
	else ddate= input(dischdate,yymmdd10.);
run;


/*NUMERATOR*/
/*RAN SUCCESSFULLY*/
proc sql;
create table numerator
as select
id,
pid,
dx,
edate as ndate,
admitdate,
dischdate
from
detailstable
where dx in &aaa_suff OR dx in &ccc_suff OR dx in &ooo_suff OR dx in &ppp_suff 
OR dx in &ddd_suff OR dx in &hhh_suff
OR 
dx in &aaa_rel OR dx in &ccc_rel OR dx in &ooo_rel OR dx in &ppp_rel OR 
dx in &diab_rel OR dx in &htn_rel)
AND edate between '01Jan2021' and '31Dec2021'
and (rev in &ED_REV or rev in &OBS_REV or cpt in &ED_CPT or cpt in &OBS_CPT) 
order by pid;
quit;


proc sql;
create table combined
as select 
id,
pid,
a.ddate,
b.ndate
from 
denom_final a inner join numerator b
on pid;
quit;
ERROR: Ambiguous reference, column pid is in more than one table.

[Tom]

This logic does not look very valid.

if ((dxpos = 1 and dx in &aaa) OR 
	(dxpos = 1 and dx in &aaa_rel AND dxpos > 1 and dx in &aaa_suff)) then condition = "AAA";

Are you sure you want to test if a single instance of DX is both the list stored in AAA_REL and in the list stored in AAA_SUFF?

If so why not just test whether DX is in the smaller list of that is the intersection of the two lists?

I suspect that you want to check if ANY of a series of DX values are in the first list and if some other DX in the series is also in the second list.  If so then you will need something different.

Please show 4 or 5 values of DX and examples of the values in the two list and say what you want to do with them?

Also how do you have many values of DX?  Are there multiple variables in a single observation?  Like DX1, DX2, etc? Or multiple observations?  Either way to test if ANY (or ALL) of the DX values are in some list you need to add more logic.

[bhca60]

I fixed it to just bring in those in position one

[SASKiwi · Accepted solution]

You need to identify which PID you want to read:

proc sql;
create table combined
as select 
id,
a.pid,
a.ddate,
b.ndate
from 
denom_final a inner join numerator b
on a.pid = b.pid;
quit;

[bhca60]

I'm getting an error and just trying to join two tables on pid:

proc sql;
create table combined
as select 
id,
pid,
a.ddate,
b.ndate
from 
denom_final a inner join numerator b
on pid;
quit;
ERROR: Ambiguous reference, column pid is in more than one table.

[Kurt_Bremser]

on pid

on its own would only test for Boolean "true" values in a single variable (not zero and not missing).

To check for the equality of the pid variables in both datasets, you need to say so:

on a.pid = b.pid

[Kurt_Bremser]

Please do not post the same question twice.