IBM Mainframe Forum

by **eric12326** » Wed Apr 24, 2013 2:10 am

Thanks this is my level of Z/OS DFSORT V1R5

001258 bradford 1999 4 139 779
001259 bradford 1999 5 175 799
001260 bradford 1999 6 182 758
001261 bradford 1999 7 225 233
001262 bradford 1999 8 205 587
001263 bradford 1999 9 207 1131
001264 bradford 1999 10 146 933
001265 bradford 1999 11 112 335
001266 bradford 1999 12 81 1804
001267 bradford 2000 1 86 716
001268 bradford 2000 2 99 720
001269 bradford 2000 3 115 532
001270 bradford 2000 4 123 1207
001271 bradford 2000 5 170 503
001272 bradford 2000 6 190 760

and sample data

by **Akatsukami** » Wed Apr 24, 2013 2:17 am

eric12326 wrote:Thanks this is my level of Z/OS DFSORT V1R5

As indicated in this post by Frank Yaeger, Mr Kolusu will need to see the ICE201I message (and preferably the rest of the messages from DFSORT) to determine the exact version, including PTFs applied. Please conquer your fears and paste the Coded messages into this thread.

by **dick scherrer** » Wed Apr 24, 2013 2:34 am

Hello,

From your sample data, what do you want as output when your process is run?

by **eric12326** » Wed Apr 24, 2013 4:06 pm

Hello D.S,

From the sample data I,m looking to generate a report for each month(24,2) indicating the best performing location(1,17) for sales(27,7) summed over a 20 yr period.

So far I can generate a report a report listing all sales/months for all years.

But what I want is a report showing the best location sales per month summed over the 20 yr period ie: 1-12

Instead I get Duplicates for all years.

Sorry if I'm not explaining myself very well, I am starting to tie myself in knots !!!!!

As mentioned I have tried SELECT, FIRST, FIRSTDUP and OCCUR to try and get the top grossing location 1-12 over all yearly sales to no avail as yet.

Currently trawling through DFSORT application programming guide for help.

Thanks Newbie

by **skolusu** » Wed Apr 24, 2013 10:25 pm

eric12326 wrote:Hello D.S,

From the sample data I,m looking to generate a report for each month(24,2) indicating the best performing location(1,17) for sales(27,7) summed over a 20 yr period. So far I can generate a report a report listing all sales/months for all years. But what I want is a report showing the best location sales per month summed over the 20 yr period ie: 1-12
Sorry if I'm not explaining myself very well, I am starting to tie myself in knots !!!!!

Eric,

You are actually confusing us , well at least me.

When you say you want the best location per month, do you ignore the year? if you do then you would get 1 record for each location. let take this sample data for 2 locations for month april but spread across 3 years.

Select all

001258 BRADFORD 1999 4 139
001258 BRADFORD 2000 4 140
001258 BRADFORD 2001 4 150

001270 BRADFORD 1999 4 300
001270 BRADFORD 2000 4 400
001270 BRADFORD 2001 4 500

When you ignore the year and sum on the location+month you end up with 2 rows like this

Select all

001258 BRADFORD 1999 4 429
001270 BRADFORD 1999 4 1200

Now do you want to pick the highest sales amount value? ie 1200?

Select all

001270 BRADFORD 1999 4 1200

You need to provide examples like I shown so that it is easy for us to understand as to what you are trying to do. Btw do you have embedded spaces for the sales amount?

by **eric12326** » Thu Apr 25, 2013 1:34 am

Sorry if I confusing matters,

My source data consists of 20 years of sales figures. This is the actual question.

"For each month, report the best performing location for car sales."

so basically what I want is the best selling location over the 20 yrs for jan,feb,march and so on.
The output should show 12 best performing locations for the 12 months summed over the 20 years.

this is a sample of what I have. I need to omit the duplicates and keep only the highest sales per month.

Select all

bradford 6 7083
braemar 8 7045
braemar 7 7043
durham 6 7039
bradford 9 6725
cardiff 8 6715
bradford 5 6091
newtonrigg 5 6066
braemar 9 5853
cambridge 10 5853
lerwick 8 5834
stornoway 9 5782
rossonwye 10 5689
leuchars 5 5643
braemar 5 5296
cambridge 4 5282

by **skolusu** » Thu Apr 25, 2013 2:11 am

Eric,

You really need to work on show what you have and what you finally need. You show the input without duplicates data for the months and you never ever show the final output you need. Any way here is a stab at it from what I gathered so far.
The layout I assumed is

Select all

Location 01-17
Month 24-25
Sales amount 27-33

Select all

//STEP0100 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//INA DD DISP=SHR,DSN=Your input file
//INB DD DISP=SHR,DSN=Same input file again
//SORTOUT DD SYSOUT=*
//SYSIN DD *
JOINKEYS F1=INA,FIELDS=(1,19,A)
JOINKEYS F2=INB,FIELDS=(1,19,A)
REFORMAT FIELDS=(F1:1,19,F2:20,5)

SORT FIELDS=(18,2,CH,A,20,5,ZD,D),EQUALS

OUTREC OVERLAY=(20:20,5,PD,M10,LENGTH=9)

OUTFIL REMOVECC,NODETAIL,BUILD=(80X),
HEADER1=(DATE,8X,'BEST PERFORMING LOCATION REPORT',
12X,'PAGE : ',PAGE,/,/,
'LOCATION',25X,'MONTH',16X,'SALES',/,
17C'-',5X,16C'-',5X,16C'-'),
SECTIONS=(18,2,HEADER3=(1,17,19X,18,2,12X,20,9)),
TRAILER1=(44:16C'-',/,4:'TOTAL SALES : ',
50:TOT=(20,9,UFF,M10,LENGTH=10))
//*
//JNF1CNTL DD *
INREC BUILD=(01,17, $ LOCATION
24,02) $ MONTH
SUM FIELDS=NONE $ REMOVE DUPS
//*
//JNF2CNTL DD *
INREC BUILD=(01,17, $ LOCATION
24,02, $ MONTH
27,7,UFF,PD,LENGTH=5) $ SALES AMOUNT
SUM FIELDS=(20,5,PD) $ SUM SALES AMOUNT
//*

The output will be something like this

Select all

04/24/13 BEST PERFORMING LOCATION REPORT PAGE : 1

LOCATION MONTH SALES
----------------- ---------------- ----------------
CAMBRIDGE 4 5282
NEWTONRIGG 5 6066
DURHAM 6 7039
BRAEMAR 7 7043
LERWICK 8 5834
STORNOWAY 9 5782
CAMBRIDGE 10 5853
----------------
TOTAL SALES : 99039

If that is not what you want you need to show the Sample INPUT and DESIRED OUTPUT

by **eric12326** » Thu Apr 25, 2013 2:57 am

thanks this looks close to what I need and should give me something to work with.

I appreciate all the help and your patients .

Newbie

by **skolusu** » Thu Apr 25, 2013 3:47 am

I had a typo in the control cards

The sort fields statement should be sorting the sales amount in PD format rather than ZD format. Sorry about that.

Select all

SORT FIELDS=(18,2,CH,A,20,5,PD,D),EQUALS

IBM Mainframe Forum

ICETOOL

Re: ICETOOL

Re: ICETOOL

Re: ICETOOL

Re: ICETOOL

Re: ICETOOL

Re: ICETOOL

Re: ICETOOL

Re: ICETOOL

Re: ICETOOL