topic Re: Reduce the length of the numeric variables in DI Studio in SAS Data Management

Reduce the length of the numeric variables in DI Studio

Babloo — Mon, 09 Apr 2018 08:01:21 GMT

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.

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

Kurt_Bremser — Mon, 09 Apr 2018 08:19:57 GMT

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?

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

RW9 — Mon, 09 Apr 2018 08:22:27 GMT

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.

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

Babloo — Mon, 09 Apr 2018 08:27:37 GMT

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?

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

Kurt_Bremser — Mon, 09 Apr 2018 08:33:43 GMT

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?

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

Kurt_Bremser — Mon, 09 Apr 2018 08:36:37 GMT

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;

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

Babloo — Mon, 09 Apr 2018 08:38:37 GMT

Got it now! How can I tackle this issue now?

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

Kurt_Bremser — Mon, 09 Apr 2018 08:47:00 GMT

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

How about using three (3) as the length?

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

ballardw — Mon, 09 Apr 2018 15:50:17 GMT

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.

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

Tom — Mon, 09 Apr 2018 16:26:49 GMT

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