topic left join in SAS Programming

left join

sg_kr — Wed, 16 Jan 2019 11:31:07 GMT

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

Re: left join

PeterClemmensen — Wed, 16 Jan 2019 11:50:45 GMT

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;

Re: left join

sg_kr — Wed, 16 Jan 2019 12:09:51 GMT

sorry its typo error.

its referred as a in the code.

Re: left join

PeterClemmensen — Wed, 16 Jan 2019 12:12:05 GMT

Ok. Can you post your log please?

Re: left join

Kurt_Bremser — Wed, 16 Jan 2019 12:55:19 GMT

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.