Everyone,

It's been a few years since I asked for help on a rolling five year percentage of change (see my post dated June 2017). Now, I need to take what I learned and add another element, a small-cell masking procedure. Below is my code.

I know the problem lies in how the data is stored in terms of its format after the masking procedure is done (see phase 5 in the code below) but before I try to run the calculations for the rolling five years.

I've attempted to convert the data multiple times and multiple ways. I'm struggling with it (see *NEED TO CONVERT &MEASURE TO FIELD FOR CALCULATION). I've read several forum posts about converting data from one format to another, but so far, nothing has led me to success.

Originally, I started my fex by running the calculations and then adding the masking, but WebFocus kept eliminating data altogether and not including it in totals. So, I think my sequencing of small cell masking and then adding my calculations appears to be correct. It's just figuring out the proper way to convert that field.

Does anyone have any ideas?

 SET EXCELSERVURL = ''
SET EMGSRV=OFF
SET MESSAGE = OFF
SET ERROROUT = OFF
SET WARNING = OFF
SET TRMOUT = OFF
SET MSG = OFF
SET ASNAMES = ON
SET HOLDATTR = ON
SET EMPTYREPORT = ON
SET NODATA = '^'

DEFINE FILE STAFF_AGE
CYSET/I4 = EDIT(CALENDAR_YEAR);
YR /YY = CYSET;
SORT_STORE/A66V = SORT_NAME;
END

-SET &MAXYR = &YR;
-SET &MINYR = &MAXYR-5;

TABLE FILE STAFF_AGE
-* SET values for small protect processing and -INCLUDE.
-* Some choices are optional.
-*INCLUDE small_cell_protect_5
-*-----------------------------------------------------------------------------------------------
SUM
SUM.STAFF_AGE.STAFF_AGE.COUNT/P9C AS 'SC_COUNTS'
BY  STAFF_AGE.STAFF_AGE.REGION
BY  STAFF_AGE.STAFF_AGE.SORT_STORE
BY  STAFF_AGE.STAFF_AGE.CALENDAR_YEAR
BY  STAFF_AGE.STAFF_AGE.YR
BY  LOWEST STAFF_AGE.STAFF_AGE.AGE_GROUPS
WHERE (STAFF_AGE.STAFF_AGE.QUARTER_NAME EQ 'First Quarter' )
WHERE STAFF_AGE.STAFF_AGE.SORT_STORE EQ 'Julie's Crafts and Hobbies';
WHERE (STAFF_AGE.STAFF_AGE.YR GE &MINYR);
WHERE (STAFF_AGE.STAFF_AGE.YR LE &MAXYR);
ON TABLE HOLD AS EXTDATA FORMAT FOCUS
END
-RUN

-* EXTRACT MAXIMUM YEAR IN A VARIABLE
-DEFAULTH &YRMAX = 0
TABLE FILE EXTDATA
SUM MAX.YR/I4 AS 'YRMAX'
ON TABLE HOLD AS MAXYR FORMAT BINARY
END
-RUN
-READFILE MAXYR
-SET &YRMAX = &YRMAX;
-TYPE &YRMAX

-* EXTRACT ALL INDIVIDUAL YEARS IN A VARIABLE
-DEFAULTH &EXTYR = 0
TABLE FILE EXTDATA
BY YR/I4 AS 'EXTYR'
ON TABLE HOLD AS YRLIST FORMAT BINARY
END
-RUN

-SET &NBYR = &LINES;

-REPEAT READYR FOR &I FROM 1 TO &NBYR STEP 1
-DEFAULTH &YR.&I = 0
-READFILE YRLIST
-SET &YR.&I = &EXTYR;
-TYPE &YR.&I
-READYR

DEFINE FILE EXTDATA
CALENDAR_YEAR/A40V =EDIT(YR);
END

TABLE FILE EXTDATA
SUM
SC_COUNTS
BY REGION
BY SORT_STORE
BY YEAR
-*BY YR
BY AGE_GROUPS
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE HOLD AS REPINPUT FORMAT FOCUS
-*ON TABLE PCHOLD FORMAT HTML
END

-* SET values for small protect processing and -INCLUDE.
-* Some choices are optional.
-SET &input   = 'repinput';
-SET &output  = 'finaldata';
-SET &dim1    = 'SORT_STORE';
-SET &dim1_id = 'AGE_GROUPS';
-SET &dim2    = 'CALENDAR_YEAR';
-SET &measure = 'SC_COUNTS';


-*-----------------------------------------------------------------------------------------------
-* Phase 0 - Initialize
-* Establish default values. This defaulting sytax requires calling proceudre to -SET
-* to override the default parameters.

-DEFAULTH &small   = '5';
-DEFAULTH &tsmall  = '3';
-DEFAULT &input   = 'input';
-DEFAULT &output  = 'output';
-DEFAULT &dim1    = ' ';
-DEFAULT &dim1_id = '&dim1.EVAL';
-DEFAULT &dim2    = ' ';
-DEFAULTH &dim2_id = '&dim2.EVAL';
-DEFAULT &measure = ' ';

 SET ASNAME=ON,HOLDLIST=PRINTONLY
 FILEDEF small_cell_data DISK small_cell_data.ftm (APPEND

 DEFINE FILE &input.EVAL
 DIM1_ID/A100 = &dim1_id.EVAL;
 DIM1/A100    = &dim1.EVAL;
 DIM2_ID/A100 = &dim2_id.EVAL;
 DIM2/A100    = &dim2.EVAL;
 MEASURE/D20 MISSING ON  = &measure.EVAL ;
 END

-*-----------------------------------------------------------------------------------------------
-* Phase 1 - Dim Orders
-* Assign a number to each distinct dimension value and append a Total entry with the last value.
-* This is used for sorting the rows and columns in the final report, including the totals which
-* are calculated in the reporting data (not doing ROW-TOTAL and COLUMN-TOTAL features).
 TABLE FILE &input.EVAL
 SUM     DIM1 NOPRINT
 COMPUTE DIM1_SORT/I11 = IF LAST DIM1 EQ DIM1 THEN DIM1_SORT ELSE DIM1_SORT + 1;
      BY DIM1
 ON TABLE HOLD AS dim1 FORMAT FOCUS INDEX DIM1
 END
 SQL INSERT INTO dim1 VALUES ( 'Total' , 999999999 );
 END

 TABLE FILE &input.EVAL
 SUM     DIM2 NOPRINT
 COMPUTE DIM2_SORT/I11 = IF LAST DIM2 EQ DIM2 THEN DIM2_SORT ELSE DIM2_SORT + 1;
      BY DIM2
 ON TABLE HOLD AS dim2 FORMAT FOCUS INDEX DIM2
 END
 SQL INSERT INTO dim2 VALUES ( 'Total' , 999999999 );
 END

-*-----------------------------------------------------------------------------------------------
-* Phase 2 - Main Data
-* Define a counter SMALL_COUNTER for small values. Define NONSMALL_TOTAL for summing all non-small values.
 DEFINE FILE &input.EVAL ADD
 SMALL_COUNTER/I11  = IF MEASURE LT &small AND MEASURE IS NOT MISSING THEN 1       ELSE 0;
 NONSMALL_TOTAL/I11 = IF MEASURE GE &small THEN MEASURE ELSE 9999999;
 END

 TABLE FILE &input.EVAL
 PRINT   MEASURE
         SMALL_COUNTER
         NONSMALL_TOTAL
 COMPUTE DETAIL/A1 = 'D';
      BY DIM1_ID
      BY DIM1
      BY DIM2_ID
      BY DIM2
 ON TABLE HOLD AS small_cell_data FORMAT ALPHA
 END

-*-----------------------------------------------------------------------------------------------
-* Phase 3 - Row/Col Totals
-* Calculate totals for both dimensions (what will become totals row and column in the final
-* report) and append to previous hold file.
 TABLE FILE &input.EVAL
 SUM     DIM1_ID
         DIM1
 COMPUTE DIM2_ID/A100 = ' ';
 COMPUTE DIM2/A100   = 'Total';
         MEASURE
		 SMALL_COUNTER
         NONSMALL_TOTAL
 COMPUTE DETAIL/A1 = 'T';
      BY DIM1 NOPRINT
      BY DIM2 NOPRINT
 ON TABLE HOLD AS small_cell_data FORMAT ALPHA
 END

 TABLE FILE &input.EVAL
 SUM
 COMPUTE DIM1_ID/A100 = 'Total';
 COMPUTE DIM1/A100   = DIM1;
         DIM2_ID
         DIM2
         MEASURE
		 SMALL_COUNTER
         NONSMALL_TOTAL
 COMPUTE DETAIL/A1 = 'T';
      BY DIM2 NOPRINT
      BY DIM1 NOPRINT
 ON TABLE HOLD AS small_cell_data FORMAT ALPHA
 END

-*-----------------------------------------------------------------------------------------------
-* Phase 4 - Join Sorts
-* Join to dim sort tables to get proper column and row sorting, including Total row and column
-* at the bottom and right.
 JOIN DIM1 IN small_cell_data TO DIM1 IN dim1 AS J1
 JOIN DIM2 IN small_cell_data TO DIM2 IN dim2 AS J2

-*-----------------------------------------------------------------------------------------------
-* Phase 5 - Final Logic
-* Join to dim sort tables to get proper column and row sorting, including Total row and column
-* at the bottom and right.
SET &ECHO='ALL';
 TABLE FILE small_cell_data
 SUM
 	COMPUTE MIN_VALUE/I11 =  MIN.NONSMALL_TOTAL;
	COMPUTE COUNTER/I11 =  SUM.SMALL_COUNTER/2;
	BY DIM2_ID
	BY DIM1
 PRINT
 DIM1           AS '&dim1'
 DIM1_ID        AS '&dim1_id'
 DIM1_SORT      AS '&dim1._SORT'
 DIM2           AS '&dim2'
 DIM2_ID        AS '&dim2_id'
 DIM2_SORT      AS '&dim2._SORT'
 DETAIL
 MEASURE
 SMALL_COUNTER
 NONSMALL_TOTAL
                                                                                           0;
-*updated I11 to d11 to shift from integer to decimal in order to display comma formatting
 COMPUTE FMEASURE/A100 =
         IF DETAIL  EQ 'D' AND MEASURE LT &small 			THEN '^'    ELSE
		 IF COUNTER EQ  1 AND  MEASURE EQ MIN_VALUE 		THEN '^'	ELSE
		 FPRINT(MEASURE, 'D11', 'A40');     AS '&measure'
BY DIM2_ID
BY DIM1

 ON TABLE HOLD AS &output
 ON TABLE SET STYLE *
 END
-RUN

-* Create report based on small protect output.

TABLE FILE finaldata

    SUM &measure
BY	 &dim1._SORT NOPRINT
-*BY REGION
BY &dim1   AS 'Store'
BY &dim1_id   AS 'Age Group'
BY &dim2
WHERE &dim2 NE 'Total'
-*ON &dim1
-*ON TABLE HOLD AS RPTDATA FORMAT FOCUS
ON TABLE PCHOLD FORMAT HTML
END
-RUN

-*NEED TO CONVERT &MEASURE TO FIELD FOR CALCULATION

DEFINE FILE RPTDATA
SC_COUNTS2/D9=ATODBL(SC_COUNTS,'8','D9');
SC_COUNTS3/P10.1=SC_COUNTS2;
END

 -*CALCULATE INCREASE PCT FOR DETAILED ROWS
TABLE FILE RPTDATA
SC_COUNTS3
	COMPUTE LAG_A1/D11 MISSING ON = IF AGE_GROUPS EQ LAST AGE_GROUPS THEN SC_COUNTS3 ELSE MISSING;
	COMPUTE LAG_A2/P9C MISSING ON = IF AGE_GROUPS EQ LAST AGE_GROUPS THEN LAST LAG_A1 ELSE MISSING;
	COMPUTE LAG_A3/P9C MISSING ON = IF AGE_GROUPS EQ LAST AGE_GROUPS THEN LAST LAG_A2 ELSE MISSING;
	COMPUTE LAG_A4/P9C MISSING ON = IF AGE_GROUPS EQ LAST AGE_GROUPS THEN LAST LAG_A3 ELSE MISSING;
	COMPUTE LAG_A5/P9C MISSING ON = IF AGE_GROUPS EQ LAST AGE_GROUPS THEN LAST LAG_A4 ELSE MISSING;
	COMPUTE ONEPCTCHG/P6% MISSING  ON = IF LAG_A1 GT 0 AND SC_COUNTS3 GT 0 THEN (SC_COUNTS3 - LAG_A1)/ LAG_A1 * 100 ELSE MISSING;
	COMPUTE FIVEPCTCHG/P6% MISSING ON = IF LAG_A5 GT 0 AND SC_COUNTS3 GT 0 THEN (SC_COUNTS3 - LAG_A5)/ LAG_A5 * 100 ELSE MISSING;
BY  TOTAL COMPUTE ROWID/I2 = 1;
BY STORE
BY AGE_GROUPS/A9
BY CALENDAR_YEAR
WHERE AGE GROUP NE 'Total';
ON  TABLE HOLD AS DETDATA FORMAT FOCUS
ON TABLE PCHOLD FORMAT HTML
END
-RUN

TABLE FILE DETDATA
PRINT
   SC_COUNTS3
   LAG_A1
   LAG_A2
   LAG_A3
   LAG_A4
   LAG_A5
   ONEPCTCHG
   FIVEPCTCHG
BY STORE
BY ROWID
BY AGE_GROUPS
BY CALENDAR_YEAR
ON TABLE HOLD AS RPTDATA FORMAT FOCUS
END
-RUN
-GOTO LBL_PRINT;

-*Print section starts here
-LBL_PRINT

-DEFAULTH &YR1 = 0, &YR2 = 0, &YR3 = 0, &YR4 = 0, &YR5 = 0, &YR6 = 0;

DEFINE FILE RPTDATA
VYSET/I4 = EDIT(CALENDAR_YEAR);
YR /YY = CYSET;

COL1/D12C MISSING ON = IF YR EQ &YR1.EVAL THEN SC_COUNTS3 ELSE MISSING;
COL2/D12C MISSING ON = IF YR EQ &YR2.EVAL THEN SC_COUNTS3 ELSE MISSING;
COL3/D12C MISSING ON = IF YR EQ &YR3.EVAL THEN SC_COUNTS3 ELSE MISSING;
COL4/D12C MISSING ON = IF YR EQ &YR4.EVAL THEN SC_COUNTS3 ELSE MISSING;
COL5/D12C MISSING ON = IF YR EQ &YR5.EVAL THEN SC_COUNTS3 ELSE MISSING;
COL6/D12C MISSING ON = IF YR EQ &YR6.EVAL THEN SC_COUNTS3 ELSE MISSING;
COL7/P6C% MISSING ON = IF YR EQ &YR6.EVAL THEN ONEPCTCHG ELSE MISSING;
COL8/P6C% MISSING ON = IF YR EQ &YR6.EVAL THEN FIVEPCTCHG ELSE MISSING;
END

TABLE FILE RPTDATA
SUM
	COL1 AS '&YR1.EVAL'
	COL2 AS '&YR2.EVAL'
	COL3 AS '&YR3.EVAL'
	COL4 AS '&YR4.EVAL'
	COL5 AS '&YR5.EVAL'
	COL6 AS '&YR6.EVAL'
	COL7 AS '1 Year Chg'
	COL8 AS '5 Year Chg'
BY	Store AS 'Store'
BY  ROWID   NOPRINT
BY	AGE_GROUPS AS 'Age Groups'
ON TABLE SUBHEAD
"Hobbies USA"
"Sales Staff by Age"
"Calendar Years &MINYR - &MAXYR"
FOOTING
"Source: HR Records"
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
  INCLUDE=IBFS:/WFC/Repository/Style.sty,$
$
END
-RUN
-LBL_END;
 

This message has been edited. Last edited by: FP Mod Chuck,

This is a lot of code to follow, do you have a simpler representation of the problem you're trying to solve? Also, in phase 5, you need to change your syntax to say:

-SET &ECHO=ALL;

quote:

Originally posted by BabakNYC:
This is a lot of code to follow, do you have a simpler representation of the problem you're trying to solve? Also, in phase 5, you need to change your syntax to say:

-SET &ECHO=ALL;

I do agree, a simpler code may help.
Furthermore, as is, your code seems not to work top to bottom. It include steps (TABLE FILE) with PCHOLD and some with HOLD...

quote:

NEED TO CONVERT &MEASURE TO FIELD FOR CALCULATION

If you need to perform other calculation on your masked field why not keep both : a masked field and a value field ?
Then on final report you just use (display/show) the one that you need (masked one).

Also, why "0;" ?

 NONSMALL_TOTAL
                                                                                           0;
-*updated I11 to d11 to shift from integer to decimal in order to display comma formatting