I'm struggling to get the logic for below scenario. I will explain with a scenario.

I have data in the table as below. The table can have either more than one row for an year (different dates) or no entry for an year. I would like to create a hold file as mentioned below for the latest three years. If the table has more than one row for an year, need to consider the latest record(latest date). If you don't have record for an year, copy the count from earlier available year's participant count. Can somebody please advice how can I create the hold file.

Table A:

Date Participant_count
12/31/2014 70
08/30/2017 65
12/31/2017 80

HOLD File:

Year Count
2016 70
2017 80
2018 80

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

Hi Jarves,
Filling in the gaps in the data was addressed some 20 years ago by Noreen Redden of FocWizard fame, using the MacGuyver technique. Here is an implementation.

  
-SET &ECHO=ALL;
-* File jarves01.fex
-* Parameters: start date, number of years, number of output years
-DEFAULT &START=2000, &YEARS=19, &OUTPUT=3
FILEDEF JARVES DISK JARVES.FTM
-RUN
-* Jarves data
-WRITE JARVES 12/31/2014 70
-WRITE JARVES 08/30/2017 65
-WRITE JARVES 12/31/2017 80
-CLOSE JARVES
-RUN
-* JARVES master file
EX -LINES 6 EDAPUT MASTER,JARVES,C,MEM
FILENAME=JARVES, SUFFIX=FIX
SEGNAME=JARVES, SEGTYPE=S0
FIELDNAME=JDM, USAGE=A6, ACTUAL=A6,$
FIELDNAME=JYEAR, USAGE=I4, ACTUAL=A4,$
FIELDNAME=PCOUNT, USAGE=I3, ACTUAL=A3,$
-RUN
-* Use MacGuyver technique to fill gaps between dates
JOIN BLANK WITH JYEAR IN JARVES TO BLANK IN FSEQ AS M_
DEFINE FILE JARVES
BLANK/A1 WITH JYEAR=' ';
BEGIN/I4=&START;
RYEAR/I4=BEGIN + COUNTER  -1;
RCOUNT/I3=IF RYEAR EQ JYEAR THEN PCOUNT ELSE 0;
END
-* Produce HOLD file with all the years
TABLE FILE JARVES
PRINT RCOUNT
BY HIGHEST RYEAR
IF COUNTER LE &YEARS
ON TABLE HOLD AS H1
END
-RUN
-* Get last value for each year
DEFINE FILE H1
TCOUNT/I3=IF RYEAR NE LAST RYEAR THEN RCOUNT ELSE IF RCOUNT NE 0 THEN RCOUNT ELSE LAST TCOUNT;
END
TABLE FILE H1
SUM LST.TCOUNT
BY RYEAR
ON TABLE HOLD AS H2
END
-RUN
-* Reduce HOLD file to last value
TABLE FILE H2
SUM COMPUTE LCOUNT/I3=IF TCOUNT NE 0 THEN TCOUNT ELSE LAST LCOUNT;
BY RYEAR
ON TABLE HOLD AS H3
END
-RUN
-* Output
TABLE FILE H3
PRINT LCOUNT
BY HIGHEST &OUTPUT RYEAR
END

MacGuyver FSEQ master (change the directory from FOCALPOINT to the name of the directory where fseq.txt is placed:

  
 FILE=FSEQ, SUFFIX=FIX, DATASET=FOCALPOINT/FSEQ.TXT, $
  SEGNAME=SEG1
   FIELD=CONTROL, BLANK , A1, A1, $
  SEGNAME=SEG2, PARENT=SEG1, OCCURS=VARIABLE
   FIELD=CHAR, , A1, A1, $
   FIELD=COUNTER, ORDER, I4,  I4,$

MacGuyver data file, fseq.txt. IMPORTANT: the first character is a space!!!

  
 FSEQFILEFORMCGUYVERFSEQFILEFORMCGUYVERFSEQFILEFORMCGUYVERFSEQFILEFORMCGUYVER
  

As with all thing FOCUS, and we use MacGyver a lot, we've got a permanent .FOC MacGyver file in baseapp. Here's the master and code to build the file.

FILE=MACGYVER,SUFFIX=FOC
SEGNAME=MAC1,SEGTYPE=S1,DATASET='/opt/ibi/apps/baseapp/macgyver.foc',$
  FIELD=BLANK,,A1,INDEX=I,$
SEGNAME=MAC2,SEGTYPE=S1,PARENT=MAC1
  FIELD=COUNTER,ORDER,I4,$


-* File loadmac.fex
-*
-* Build MACGYVER.FOC
-*
-DEFAULT &HOWMANY=600;
 CREATE FILE MACGYVER
 MODIFY FILE MACGYVER
 COMPUTE CTR/I9=;
 FIXFORM &HOWMANY(CTR/4 X-4)
 COMPUTE
   BLANK=' ';
   COUNTER=IF COUNTER EQ 0 THEN CTR ELSE COUNTER+1;
 MATCH BLANK
    ON MATCH CONTINUE
    ON NOMATCH INCLUDE
 MATCH COUNTER
    ON MATCH CONTINUE
    ON NOMATCH INCLUDE
 DATA
1
 END
-RUN

Just set &HOWMANY to the max number of rows needed in the MAC2 segment, and yes, we need 600 for one of our processes.