DATA Step, Macro, Functions and more

Losing observations when doing a left outer join

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 11
Accepted Solution

Losing observations when doing a left outer join

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


Accepted Solutions
Solution
‎06-24-2016 04:08 PM
Respected Advisor
Posts: 4,651

Re: Losing observations when doing a left outer join

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.

PG

View solution in original post


All Replies
Solution
‎06-24-2016 04:08 PM
Respected Advisor
Posts: 4,651

Re: Losing observations when doing a left outer join

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.

PG
Occasional Contributor
Posts: 11

Re: Losing observations when doing a left outer join

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

 

 

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 247 views
  • 0 likes
  • 2 in conversation