Dividing in SORT

Jenifer Lewis · New User Joined: 14 Sep 2009 Posts: 28 Location: Maine

I would like to divide a number in a file by another number in the same file and include the result in the sort output, but I am not getting the results I want.

For example, dividing "026392" by "000997" yields " 26" with several blanks before the 26.

I've coded it this way: (15,6,ZD,DIV,38,6,ZD)

I suspect all those leading blanks are trying to tell me something, and I tried using masks (e.g. M2) but I got a syntax error.

I'm clearly missing something fairly basic.

Originally I was trying to divide the numbers the other way round to get a percentage, and with the results all to the right of the decimal point the output field was just zeros.

I'd appreciate any thoughts, ideas, or manual pages you can provide.

Thanks!

Frank Yaeger · Posted: Fri Feb 26, 2010 4:12 am

What do you want the output value to look like for your example?

Show a better example of input values and expected output values.

gcicchet · Posted: Fri Feb 26, 2010 4:26 am

Hi,

Jenifer Lewis · New User Joined: 14 Sep 2009 Posts: 28 Location: Maine

Forget my earlier example, as it's just one of several feeble attempts.

I want a percentage of the number of customer calls within a group of billed accounts. In the following example, of the 26,392 accounts which billed a couple of weeks ago, 997 of the customers called within a 14-day period.

Here is an input record, and yes, the fields are labeled:

TOTAL BILL = 026392 BILL & CALL = 000997 % OF BILLED =

The result of dividing the smaller by the larger is .037776598 which I would like to see as 3.78 or something similar.

Thanks!

Frank Yaeger · Posted: Fri Feb 26, 2010 5:25 am

What do you want the output to look like exactly?

Please show the output you expect for each of the following input records:

TOTAL BILL = 026392 BILL & CALL = 000997 % OF BILLED =
TOTAL BILL = 026392 BILL & CALL = 026392 % OF BILLED =
TOTAL BILL = 026392 BILL & CALL = 009999 % OF BILLED =

I'm trying to find out exactly what format you want the output in.

Also, what is the RECFM and LRECL of the input file and expected output file?

If the first field 14,6,ZD or something else (what)?
Is the second field 35,6,ZD or something else (what)?

What is the starting position, length and format of the output % field?

Jenifer Lewis · New User Joined: 14 Sep 2009 Posts: 28 Location: Maine

For these two i/p records:

TOTAL BILL = 026392 BILL & CALL = 000997
TOTAL BILL = 026392 BILL & ADJ = 000227

I'd like to see this o/p record:

TOTAL BILL = 026392 BILL & CALL = 000997 % OF BILLED = 3.78
TOTAL BILL = 026392 BILL & ADJ = 000227 % OF BILLED = 0.86

My routine formats the o/p as the first 40 bytes of the i/p record, a third label in position 42 ("% of billed") and a percentage calculated by dividing i/p field (35,6,ZD) by i/p field (14,6,ZD).

LRECL=80, RECFM=FB

I was asked to come up with something quick (isn't it always the way?) and rather than write a COBOL program on the fly which I knew darn well would change once they began seeing the results, I used DATACOM queries to derive the data, ICETOOL to match and splice, and for the last piece I thought I'd try the above.

I've never used any SORT calculation except SUM, and looking through the manuals has given me lots of ideas and parameters, but I'm stuck here. If you could steer me in the right direction for finding a good resource for figuring this stuff out, I'd be grateful.

I appreciate your help, Frank!

Frank Yaeger · Posted: Fri Feb 26, 2010 9:53 pm

Since DFSORT only does integer arithmetic, it's a matter of scaling and rounding to get what you want. Here's a DFSORT job that will do it:

Jenifer Lewis · New User Joined: 14 Sep 2009 Posts: 28 Location: Maine

Thank you, Frank -- that worked like a charm.

The OVERLAY command is nifty -- haven't had the need to try that before but it was just what the doctor ordered.

I'm looking at your code and I think I understand everything except one part. What does the bolded part of this do?

(((35,6,ZD,MUL,+100000),DIV,14,6,ZD),ADD,+5),DIV,+10,

Is it allowing for decimal places?

Frank Yaeger · Posted: Fri Feb 26, 2010 11:34 pm

(x+5)/10 is used to round the last decimal place. Without it, you would get 3.77 instead of 3.78.

Jenifer Lewis · New User Joined: 14 Sep 2009 Posts: 28 Location: Maine

Clear as mud. ;-)

As soon as we come out of storm mode (I work for a power company in Maine and we have about 130K outages right now) and things settle down to a dull roar I'll try to spend some time with Mr. Overlay and learn more about all those instructions.

Thanks again!

Frank Yaeger · Posted: Sat Feb 27, 2010 12:06 am

Jenifer Lewis · New User Joined: 14 Sep 2009 Posts: 28 Location: Maine

Yep, I see it now.

For all I know the client will want to see 4% so as not to be troubled with all those pesky decimal places, but better to err on the side of too much precision than too little.

Your explanation has been quite, um, illuminating.

And now, my shift in the storm rotation is coming to an end and I will be going home soon. Oh, how I do hope they decide to take us out of "storm mode" before my next turn in the five-person rotation comes around at midnight on Sunday morning!

Thank you for all your help and explanations. Between this and ICETOOL I'm doing all kinds of neat stuff with flat files. Who says you can't teach an old dinosaur new tricks?

dick scherrer · Posted: Sat Feb 27, 2010 12:45 am

Frank Yaeger · Posted: Sat Feb 27, 2010 1:47 am

Jenifer,

Glad I could help.

Dick,

But, of course, there were young dinosaurs when there were dinosaurs. I wonder how much a baby Tyrannosaurus weighed? (I know, I could look it up but I'm feeling lazy today.)

Craq Giegerich · Posted: Sat Feb 27, 2010 2:02 am

Frank Yaeger · Posted: Sat Feb 27, 2010 2:04 am

I suppose it would have been a bit bigger than one of my pet rats.

(I guess it's off-topic Friday.)