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

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

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
  • 2707 views
  • 0 likes
  • 2 in conversation