Join not working in SAS EG Query Builder

[JediApprentice]

I'm attempting to do a join on two datasets on an authorization number in query builder. One of the datasets was imported from a spreadsheet. Here is a picture of the proc contents on each:

Dataset 1:

Dataset 2: 

As you can see, the AUTHORIZATIONNUMBER variable in both datasets has the same type, length, format and informat. If I test on dummy datasets like this it works:

data test;
  infile datalines;
  input AUTHID 16.;
  format AUTHID 16.;
  datalines;
1111111111111111
2222222222222222
3333333333333333
4444444444444444
5555555555555555
;;
run;

data test2;
  infile datalines;
  input AUTHID 16.;
  format AUTHID 16.;
  datalines;
6666666666666666
2222222222222222
8888888888888888
8888888888888888
5555555555555555
;;
run;

If you inner join here it will come back with results, but for some reason it is not working with my two real datasets.

[TomKari]

The biggest number you can accurately represent in the SAS numeric data type is 15 signigicant digits. Once you have 16, your numbers will possibly be different than you expect.

Same problem in Excel. Try this: In a cell, type in 1234567890123456 (a 16 digit number). Set the display to number, not scientific, and get rid of decimals.

Say your cell is A1. For A2 to A50, enter the formula =A1+1

You'll see how things get messed up.

Tom

[ballardw]

Any of your values larger than 9007199254740992? That is the largest integer SAS will represent accurately in Windows or Unix.

If you are not going to do any calculations involving the authorizationnumber better would be to bring it into SAS as a character variable.

[TomKari]

Interesting! I just picked any old 16 digit number, and it failed in Excel. SAS has higher limits.

Here's a new example that will demonstrate the failure in SAS.

Tom

data have;

format sn 16.;

sn = 9007199254740982; output;

sn = 9007199254740983; output;

sn = 9007199254740984; output;

sn = 9007199254740985; output;

sn = 9007199254740986; output;

sn = 9007199254740987; output;

sn = 9007199254740988; output;

sn = 9007199254740989; output;

sn = 9007199254740990; output;

sn = 9007199254740991; output;

sn = 9007199254740992; output;

sn = 9007199254740993; output;

sn = 9007199254740994; output;

sn = 9007199254740995; output;

sn = 9007199254740996; output;

sn = 9007199254740997; output;

sn = 9007199254740998; output;

sn = 9007199254740999; output;

sn = 9007199254741000; output;

sn = 9007199254741001; output;

sn = 9007199254741002; output;

sn = 9007199254741003; output;

sn = 9007199254741004; output;

sn = 9007199254741005; output;

sn = 9007199254741006; output;

sn = 9007199254741007; output;

sn = 9007199254741008; output;

sn = 9007199254741009; output;

sn = 9007199254741010; output;

sn = 9007199254741011; output;

sn = 9007199254741012; output;

sn = 9007199254741013; output;

sn = 9007199254741014; output;

sn = 9007199254741015; output;

sn = 9007199254741016; output;

sn = 9007199254741017; output;

sn = 9007199254741018; output;

sn = 9007199254741019; output;

sn = 9007199254741020; output;

sn = 9007199254741021; output;

sn = 9007199254741022; output;

sn = 9007199254741023; output;

sn = 9007199254741024; output;

sn = 9007199254741025; output;

sn = 9007199254741026; output;

sn = 9007199254741027; output;

sn = 9007199254741028; output;

sn = 9007199254741029; output;

sn = 9007199254741030; output;

sn = 9007199254741031; output;

run;

[JediApprentice]

My bad, the length of the authid is actually 15. Not sure why 16. is the format

[JediApprentice]

@ballardw Yes but I have to join on (a.authid=b.authid) so these can't be character.

[TomKari]

1. Are these keys integer, or do they have decimals. You are guaranteed to have problems trying to do an equality join on numbers with decimals.

2. I guarantee that integers with 15 digits will be fine in SAS. I can't guarantee Excel. I suggest you find a case where the join should work, and manually examine both numbers at each stage.

Tom