topic Re: Losing observations when doing a left outer join in SAS Programming

Losing observations when doing a left outer join

janet0102 — Fri, 24 Jun 2016 17:59:42 GMT

I need to join all the variables in one dataset (caper10.caperfy10_selectvar; 17,407,272 obs) with specified variables in a 2nd dataset (temp1.fy1011_pnpts_enddate; 62,451 obs and no duplicates). I've done this with multiple tables and have never lost observations, but this time the joined dataset has 16,4999,282 obs.

My syntax is:

proc sql;
   create table left_outer_join as
       select *
          from caper10.caperfy10_selectvar as a left join
           temp1.fy1011_pnpts_enddate (keep = patssn paindate pnid enddate diag patssn1) as b
           on a.sponssn = b.patssn;
quit;

Both sponssn and patssn are character variables.

Any insight into why this is happening will be greatly appreciated!

I'm using SAS 9.4, 64 bit.

Thanks!

Janet

Re: Losing observations when doing a left outer join

PGStats — Fri, 24 Jun 2016 19:56:58 GMT

As far as I can tell, this should not append. How did you get the number of obs in caper10.caperfy10_selectvar? One possible explanation: Some (most, all?) databases do not remove deleted obs, they only mark them as "deleted". SAS is no exception. The count of records in a data table given by dictionary.tables field nobs can be superior to the count of logical records (nlobs) because some records have been deleted.

Re: Losing observations when doing a left outer join

janet0102 — Fri, 24 Jun 2016 20:07:56 GMT

Thanks! I ran a proc contents and got the smaller number. I'd gotten the larger number in the log after a proc sort.