IBM Mainframe Forum

by **eric12326** » Tue Apr 23, 2013 10:04 pm

Hi Guys/Girls,

I,m looking for some help to steer me in the right direction.

I have to generate a report to show the best perming shop for each month summed over a number of years ?

I am able to carry out the initial sort and from that determine overall sales for every month and every year?

But what I need is the overall best selling location per month over the number of years?

I have tried SELECT UNIQUE and OCCUR without much success

Any help would be appreciated

ps; be gentle I,m a NEWBIE

by **NicC** » Tue Apr 23, 2013 10:06 pm

Ummm, is this sort or DB2???

by **eric12326** » Tue Apr 23, 2013 10:21 pm

It is a dfsort using ICETOOL
I am now trying to use a FIRST or FIRSTDUP to select only the individual months not sure if that correct.

thanks for reply

by **dick scherrer** » Tue Apr 23, 2013 10:22 pm

Hello and welcome to the forum,

Suggest you post a bit of data and the output you want when that data is processed.

Explain how the data is stored.

by **eric12326** » Tue Apr 23, 2013 10:26 pm

Select all

//SORTA EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTIN DD DISP=SHR,DSN=UNIXXX.XDATA
//SORTOUT DD DISP=(NEW,PASS),DSN=&&TEMP,
// LRECL=41,RECFM=FB,SPACE=(27962,(20,1))
//SYSIN DD *
SORT FIELDS=(1,17,CH,A,24,2,ZD,A)
SUM FIELDS=(27,7,ZD)
/*
//SORTB EXEC PGM=ICETOOL
//TRDATA DD DISP=(OLD,DELETE),DSN=*.SORTA.SORTOUT
//SALESOUT DD DSN=&SALES,DISP=(,PASS),SPACE=(CYL,(1,1)),UNIT=3390
//REPORT DD SYSOUT=*
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//TOOLIN DD *
SELECT FROM(TRDATA) TO(SALESOUT) ON(18,2,ZD) FIRST USING(AUTH)
DISPLAY FROM(SALESOUT) LIST(REPORT) -
DATE TITLE('Best Performing Location') PAGE -
HEADER('Location') HEADER('Month') HEADER('Sales') -
ON(1,17,CH) ON(24,2,ZD) ON(27,7,ZD) BETWEEN(5) BLANK -
TOTAL('Total Sales')
/*
//AUTHCNTL DD *
SORT FIELDS=(27,7,ZD,D)
/*

this is what I have so far sorry if it a bit messy....

by **dick scherrer** » Tue Apr 23, 2013 11:26 pm

Hello,

When posting "things" that require a fixed-font (i.e. code, jcl, data, etc), using the Code tag will preserve alignment and improve readability. Your post has been "Code'd".

As mentioned before:

Suggest you post a bit of data and the output you want when that data is processed.

by **eric12326** » Tue Apr 23, 2013 11:48 pm

thank you d.sch.

by **skolusu** » Wed Apr 24, 2013 12:19 am

eric12326,

It would have helped if you posted a sample test data. Looking at your control cards, I gathered that

Select all

1- 17 location
18-19 State??
24-25 Month (01-12)
27-33 Sales amount

Now the first SORT is summing sales amount considering Location+ Month as key. If that is the case then you will not get the best performing location as the sum is not considering the State field. for example if you have this data

Select all

----+----1----+----2----+----3----+
LOCATION-AAA CA 01 1111111
LOCATION-AAA CA 01 2222222
LOCATION-AAA VA 01 3456789

As you can see the same location and same month have 2 different states. CA and VA. But your SORT would have clubbed all these 3 records together and gave you 1 record as below

Select all

LOCATION-AAA CA 01 006790122

Notice how VA location even though has a higher sales amount has completely disappeared. You should also include the field (18,2) as your sort criteria.

So present your input data and desired output and I can a show a much simpler way to do the same task. I would also like to see the level of DFSORT you have. Please run the following job and show me the complete sysout which help us determine the level of DFSORT you have.

Select all

//STEP0100 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTIN DD *
//SORTOUT DD SYSOUT=*
//SYSIN DD *
OPTION COPY
//*

by **eric12326** » Wed Apr 24, 2013 12:38 am

location 1-17
year 19-4
month 24-2
sales 27-7

from this I wish to determine the best performing location for sales for each month

by **skolusu** » Wed Apr 24, 2013 12:54 am

eric12326 wrote:location 1-17
year 19-4
month 24-2
sales 27-7

from this I wish to determine the best performing location for sales for each month

Please answer all the questions from my post along with a sample data.

IBM Mainframe Forum

ICETOOL

ICETOOL

Re: ICETOOL

Re: ICETOOL

Re: ICETOOL

Re: ICETOOL

Re: ICETOOL

Re: ICETOOL

Re: ICETOOL

Re: ICETOOL

Re: ICETOOL