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.
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!
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.
Ready to level-up your skills? Choose your own adventure.