BookmarkSubscribeRSS Feed
JediApprentice
Pyrite | Level 9

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.

6 REPLIES 6
TomKari
Onyx | Level 15

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
Super User

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
Onyx | Level 15

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
Pyrite | Level 9

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

JediApprentice
Pyrite | Level 9

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

TomKari
Onyx | Level 15

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 1610 views
  • 0 likes
  • 3 in conversation