DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

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

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.


Accepted Solutions
Solution
‎12-20-2016 10:17 AM
Frequent Contributor
Posts: 96

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

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

View solution in original post


All Replies
Solution
‎12-20-2016 10:17 AM
Frequent Contributor
Posts: 96

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

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

New Contributor
Posts: 4

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

Posted in reply to Kalind_Patel

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.

Super User
Posts: 19,789

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

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;

New Contributor
Posts: 4

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

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.

Super User
Posts: 11,343

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

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.

New Contributor
Posts: 4

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

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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 269 views
  • 0 likes
  • 4 in conversation