topic Re: Join not working in SAS EG Query Builder in SAS Enterprise Guide

Join not working in SAS EG Query Builder

JediApprentice — Tue, 11 Apr 2017 21:36:29 GMT

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.

Re: Join not working in SAS EG Query Builder

TomKari — Tue, 11 Apr 2017 22:49:52 GMT

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

Re: Join not working in SAS EG Query Builder

ballardw — Tue, 11 Apr 2017 22:51:45 GMT

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.

Re: Join not working in SAS EG Query Builder

TomKari — Wed, 12 Apr 2017 00:55:13 GMT

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;

Re: Join not working in SAS EG Query Builder

JediApprentice — Wed, 12 Apr 2017 16:25:06 GMT

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

Re: Join not working in SAS EG Query Builder

JediApprentice — Wed, 12 Apr 2017 16:27:42 GMT

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

Re: Join not working in SAS EG Query Builder

TomKari — Wed, 12 Apr 2017 18:22:20 GMT

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