SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Reduce the length of the numeric variables in DI Studio

Reply
Super Contributor
Posts: 625

Reduce the length of the numeric variables in DI Studio

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.

Super User
Posts: 9,890

Re: Reduce the length of the numeric variables in DI Studio

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?

 

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super Contributor
Posts: 625

Re: Reduce the length of the numeric variables in DI Studio

Posted in reply to KurtBremser
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?
Super User
Posts: 9,890

Re: Reduce the length of the numeric variables in DI Studio

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?

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super Contributor
Posts: 625

Re: Reduce the length of the numeric variables in DI Studio

Posted in reply to KurtBremser
Got it now! How can I tackle this issue now?
Super User
Posts: 9,890

Re: Reduce the length of the numeric variables in DI Studio


@Babloo wrote:
Got it now! How can I tackle this issue now?

How about using three (3) as the length?

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Posts: 9,890

Re: Reduce the length of the numeric variables in DI Studio

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Super User
Posts: 9,416

Re: Reduce the length of the numeric variables in DI Studio

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.

Super User
Posts: 13,321

Re: Reduce the length of the numeric variables in DI Studio

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.
Super User
Super User
Posts: 7,933

Re: Reduce the length of the numeric variables in DI Studio

[ Edited ]

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).

Ask a Question
Discussion stats
  • 9 replies
  • 238 views
  • 2 likes
  • 5 in conversation