Merging Mystery: SAS won't match two numeric keys, but SPSS will

[JLcra]

Hi,

I have a base SAS 9.4 merge conundrum that is mystifying me. I have two datasets that connect on an 8-digit ID unique to each person in my data. My merge is one to many. When I try to merge these files by ID, SAS fails to find any matches and appends the two files. Both files are sorted on ID only.

My data step, which has successfully merged different files that use the same key and come from the same source and were created using the same proc SQL code:

data work.temp;

set dataA(in=master) dataB(in=using);

by clt_id;

masteronly=master and not using;

usingonly=using and not master;

match=master=1 and using=1;

run;

I have checked both datasets, and the ID is a numeric of length 8 and format 11. in both files. I have visually inspected both files to doublecheck that at least one ID exists in both.

And, to make it more baffling, SPSS is able to correctly match the exact same files on the ID.

The only potential answer I could find online is decimal instability, but my IDs are 8 digit integers and as far as I can tell SAS is only storing them as such because the type is only length 8.

So what could conceivably be causing this?

Summary: Two files linked on 8-digit integer ID, one to many merge, same storage type for ID in both files, successfully merged using same code by same ID with other files, visually confirmed at least one match exists, and SPSS matches files successfully.

[Kalind_Patel · Accepted solution]

You are using set statement, which will append the data,

You have to use merge statement to merge the data in SAS.

data work.temp;

merge dataA(in=master) dataB(in=using);

by clt_id;

run;

For more example please refer this link:

http://www.ats.ucla.edu/stat/sas/modules/merge.htm

[JLcra]

Ugh, thank you. I knew I had to be making some stupid mistake. Of course my brain kept reading right over the set when I double checked code. Just needed another pair of eyes to see it for me.

[Reeza]

IDs should be character not numeric variables. 

Not matching can be due to numerical precision, bit as doubt it. 

I don't think this is correct, I would suggest starting with only the MERGE and By and adding your conditions one by one. 

masteronly=master and not using;
usingonly=using and not master;
match=master=1 and using=1;

[JLcra]

No, IDs can and often are numerics. And those statements weren't conditions, just creating diagnostic variables to easily show me where records come from.

The solution was a blatant dumb typo on my part, I wrote set instead of merge. The rest of the code is fine.

[ballardw]

Yes many people use numerics for Ids. Then get bitten by additions to the system where leading zeroes become significant. Or have to merge with another system that has character values. Or a new scheme comes in adding additional information to the id so the length exceeds 16 or 17 digits and starts causing problems with precision.

IMNSHO, if you aren't going to do arithmetic on a variable it only looks like a numeric value and should be character.

[JLcra]

When you work with data the size that I do, character storage requirements are a massive cost. That is why numerics are a great ID variable.