topic Re: Left join using Proc sql creates extra rows in SAS Programming

Left join using Proc sql creates extra rows

abdulla — Wed, 28 Oct 2020 02:09:58 GMT

I am using the following code to left join

proc sql;
create table want as
select *
from final3 left join gov_proxy
on final3.gvkey=gov_proxy.gvkey and
final3.fyear=gov_proxy.fyear;
quit;

I have 63,654 observations in my final3 dataset and

I have 186679 observations in my gov-proxy dataset

I want to have 63,654 dataset but I am getting 63888 dataset after deleting the duprecs after merging.

I have no duprecs in any one of the dataset before merging.

Can anyone help me with this?

Re: Left join using Proc sql creates extra rows

PGStats — Wed, 28 Oct 2020 02:38:54 GMT

You may not have any duplicate records, but you have at least 234 duplicate keys (gvkey-fyear) in gov_proxy.

Re: Left join using Proc sql creates extra rows

SASKiwi — Wed, 28 Oct 2020 02:50:01 GMT

This isn't a duplicate record problem, it is a duplicate key problem. Your join results indicate that you either have duplicate key values in either final3 or gov_proxy or both. You can test this easily:

proc sort data = final3 out = dupkey nodupkey;
  by gvkey fyear;
run;

If dupkey has less records than final3 then you know it contains duplicate key values. Then the question is should the data have duplicate key values and if not how do you wish to fix this?

Re: Left join using Proc sql creates extra rows

Ksharp — Wed, 28 Oct 2020 03:17:24 GMT

Or you could have missing value in gvkey or fyear .