Desktop productivity for business analysts and programmers

Join not working in SAS EG Query Builder

Reply
Frequent Contributor
Posts: 123

Join not working in SAS EG Query Builder

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:

 

Contents.PNG

 

Dataset 2: 

Contents2.PNG

 

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.

Trusted Advisor
Posts: 1,051

Re: Join not working in SAS EG Query Builder

[ Edited ]

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

Grand Advisor
Posts: 10,210

Re: Join not working in SAS EG Query Builder

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.

 

Trusted Advisor
Posts: 1,051

Re: Join not working in SAS EG Query Builder

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;

Frequent Contributor
Posts: 123

Re: Join not working in SAS EG Query Builder

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

Frequent Contributor
Posts: 123

Re: Join not working in SAS EG Query Builder

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

Trusted Advisor
Posts: 1,051

Re: Join not working in SAS EG Query Builder

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

Ask a Question
Discussion stats
  • 6 replies
  • 142 views
  • 0 likes
  • 3 in conversation