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.
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?
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 wrote:
Got it now! How can I tackle this issue now?
How about using three (3) as the length?
Or, in SAS code:
data test;
x1 = 2;
if 3 <= x1 <= 8
then answer = 'yes';
else answer = 'no';
run;
proc print data=test noobs;
run;
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.
May be of interest when setting lengths of numeric variables:
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
|
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).
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
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.