BookmarkSubscribeRSS Feed
Babloo
Rhodochrosite | Level 12

I've one numeric field which is coming from DB2 and it has a length 8 and now I want to do the mapping of this field to the target table under Join transformation and I've reduce the length to 2. When I tried to do this, I'm getting an error like, 'Length of the numeric filed should be between 3 to 8'. I tried using substr function as follows in expression tab.

 

substr(column_name,1,2)

Appreciate if someone of you help me to resolve this issue.

9 REPLIES 9
Kurt_Bremser
Super User

substr() works on CHARACTER variables, it is useless for NUMERIC variables.

 

And

'Length of the numeric filed should be between 3 to 8'

speaks for itself, doesn't it?

 

 

Babloo
Rhodochrosite | Level 12
Could you please help me understand how the error message has speak for
itself? I have the field which has integer values and has the values
between 10 to 99. Is there any function/apporoach available to tackle this
problem?
Kurt_Bremser
Super User

OK, since this is so hard for you, I'll try to translate your own post for you:

 

"and I've reduce the length to 2. When I tried to do this, I'm getting an error like, 'Length of the numeric filed should be between 3 to 8'."

 

The MINIMUM LENGTH for a numeric variable in SAS is, as you are being told by the message, 3 (three, as in I I I). The maximum length (same message) is 8 (eight, I I I I I I I I), you can count that with your fingers.

So a value of 2 (two, I I) is not allowed.

 

Got it now?

Babloo
Rhodochrosite | Level 12
Got it now! How can I tackle this issue now?
RW9
Diamond | Level 26 RW9
Diamond | Level 26

What do you mean "length"?  Numberic lengths are 3-8 by default as the error is telling you.  I really cannot tell what you are doing from that snippet of code - please learn to follow the guidance in posting a complete question to avoid confusion.  At a guess I suspect you are somehow trying to set a length to be less than 3 which is not possible for numbers.  TBH there really isn't any noticeable benefit to reducing the length from 8 to 3 other than the numbers they can contain would be lower.  You may also need to consider how to truncate the numbers if they don't fit the new storage.  A length of 3 will only allow numbers up to 8192, after that you would need to round or truncate the data.  Again, posting clear examples with test data in the form of a datastep really helps show your issues.

ballardw
Super User

May be of interest when setting lengths of numeric variables:

Largest Integer That Can Be Safely Stored in a Given Length
When Variable Length Equals ...
Largest Integer
z/OS
Largest Integer
Windows/UNIX
2
256
not applicable
3
65,536
8,192
4
16,777,216
2,097,152
5
4,294,967,296
536,870,912
6
1,099,511,627,776
137,438,953,472
7
281,474,946,710,656
35,184,372,088,832
8 (default)
72,057,594,037,927,936
9,007,199,254,740,992
 Note that if the numeric variable contain decimals it is a very poor idea to use a length less than 8.
Tom
Super User Tom
Super User

The length of a variable in SAS is how many bytes it takes in the stored dataset.  Numbers are 64 bit floating point values so they take 8 bytes.  If you know your numbers are only integers you can save some space by storing them with lengths between 3 and 7 (on IBM mainframe you can even use only two bytes).  But you do not save much space.

 

How many digits it takes to display the number is related to what display format you have attached. If your values are integers between 0 and 99 then you might want to attach the F2. format (or the Z2. format if you want values less than ten to appear with leading zeros).

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!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 9 replies
  • 2144 views
  • 2 likes
  • 5 in conversation