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
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.
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.
Thanks! I ran a proc contents and got the smaller number. I'd gotten the larger number in the log after a proc sort.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.