left join

[sg_kr]

Hi,

when i join the below tables

APC-19477289 obs

IOTC-1057 nobs

proc sql;
create table APC_1 as
select a.*, b.PROD_NAME
from APC as left join IOTC as b
on a.INS_OP_CODE =b.INS_OP_CODE and a.TYPE_COVERAGE_CODE=b.TYPE_COV_CODE;
quit;

i am getting the count of 36605480 obs in resultant dataset, i want APC count in my dataset.

could you please help

[PeterClemmensen]

Try this. In your posted code, you did not reference the table APC as a.

proc sql;
create table APC_1 as
select a.*, b.PROD_NAME
from APC as a left join IOTC as b
on a.INS_OP_CODE =b.INS_OP_CODE and a.TYPE_COVERAGE_CODE=b.TYPE_COV_CODE;
quit;

[sg_kr]

sorry its typo error.

its referred as a in the code.

[PeterClemmensen]

Ok. Can you post your log please?

[Kurt_Bremser · Accepted solution]

You have multiple matches for your key variable pairs in IOTC, causing a multiplication.

Do a

proc sort data=iotc out=test nodupkey;
by ins_op_code type_cov_code;
run;

and look at the log.