BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
janet0102
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

2 REPLIES 2
PGStats
Opal | Level 21

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
janet0102
Calcite | Level 5

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

 

 

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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