I am trying to create a report from a
fiscal year file to show only the data
for the months from the start of FY11
to the present.

(Present = today's date).

This is the synonym layout of my data:

?FF AGEDATA_11_A
-EXIT

FILENAME= AGEDATA_11_A
AGE AGE A60
_TYPE_ _TYPE_ D20.2
JUL10 jul10 D20.2
AUG10 aug10 D20.2
SEP10 sep10 D20.2
OCT10 oct10 D20.2
NOV10 nov10 D20.2
DEC10 dec10 D20.2
JAN11 jan11 D20.2
FEB11 feb11 D20.2
MAR11 mar11 D20.2
APR11 apr11 D20.2
MAY11 may11 D20.2
JUN11 jun11 D20.2

I'd like to create a date field which
I'd put into a WHERE statement
to print only the fields from July 2010
to the previous month (current_month - 1).

Anyone have any ideas?

Thanks,

Try the following:

-* FocalPoint:  create where "date" statement for limited output
-SET &TODAYIS = &YYMD ;
-* Test with different date values that are in the report as 'HIREDATE20'
-SET &TODAYIS = 20090329 ;
-SET &TODAYIS = 20090529 ;
-TYPE REPORT FROM BOQ TO &TODAYIS
-* -TYPEs are option
-* CONVERT TODAYS DATE FOR DATE MATH, IN TO: &CURDATE 
-SET &CURDATE   = DATECVT(&TODAYIS,'I8YYMD','YYMD');
-TYPE &CURDATE
-* Use &CURDATE to get the Beg Of Qtr (BOQ)
-SET &BEGTQTY0 = DATEMOV(&CURDATE, 'BOQ');
-TYPE &BEGTQTY0
-* Convert the BOQ to a useable date in the WHERE statement
-* Legend for variable, use the Capital letters: BEG This Quarter This Year
-SET &BEGTQTY  = DATECVT(&BEGTQTY0,'YYMD','A8YYMD');
-TYPE &BEGTQTY
DEFINE FILE EMPDATA
-* Convert the YY/MM/DD to an I8 for date calcs
HIREDATEI8/I8 = HIREDATE ; 
-* Add 20 YEARS to the HIREDATE to bring these dates current (just for testing)
HIREDATE20X/I8 = DATEADD(HIREDATEI8, 'Y', +20); 
HIREDATE20/I8  = DATECVT(HIREDATE20X,'YYMD','I8YYMD');
-* GET THE BOQ (BEGINNING OF THE QUARTER) FOR THE HIREDATE. (SEE WHAT QTR IT'S IN)
BEGTQTY0/I8 = DATEMOV(HIREDATEI8, 'BOQ');
-* CONVERT IT TO A YYYYMMDD FORMAT
BEGTQTY/I8  = DATECVT(BEGTQTY0,'YYMD','I8YYMD');
END
TABLE FILE EMPDATA
HEADING
"Report for hire dates from &BEGTQTY to &TODAYIS"
PRINT HIREDATE20 AS 'HireDate' FIRSTNAME AS 'First Name' LASTNAME AS 'Last Name'
-* Use the next line to verify without using the WHERE statement.
COMPUTE InThisQTR/A1 = IF HIREDATE20 FROM '&BEGTQTY' TO '&TODAYIS' THEN 'Y' ELSE '-' ; AS '?'
BY HIREDATE
-* Select only dates from BOQ to &TODAYIS (there's only one as of 10/22/2010)
-* Use the next line to limit these records.
-*WHERE HIREDATE20B FROM '&BEGTQTY' TO '&TODAYIS'
ON TABLE SET PAGE OFF
END

Something like this might work:

-SET &CURYRMTH = EDIT(&YYMD,'999999$$');
-SET &RPTYRMTH = AYM(&CURYRMTH,-1,'I6YYM');
-SET &CURYEAR  = EDIT(&YYMD,'9999$$$$');
-SET &CURMONTH = EDIT(&YYMD,'$$$$99$$');
-SET &FISCALYR = IF (&CURMONTH LE 6) THEN (&CURYEAR - 1) ELSE &CURYEAR ;
-SET &COLYRMTH = &FISCALYR | '07';
-*
TABLE FILE AGEDATA_11_A
 PRINT
-*
-REPEAT :ENDREPEAT1 13 TIMES
-SET &COLNAME = EDIT(CHGDAT('YYM','MTY',&COLYRMTH,'A17'),'999$99');
 &COLNAME.EVAL
-SET &COLYRMTH = AYM(&COLYRMTH,1,'I6YYM');
-IF (&COLYRMTH GT &RPTYRMTH) GOTO :EXITREPEAT1 ;
-:ENDREPEAT1
-:EXITREPEAT1
-*
END

Thanks Dan... We're always open to alternative methods... Now, it's up to Tomsweb to pick one, or some of each merged in with his own expertise...

Dan,

I like your method, but in this case I need to sort by the first 2 fields in the master file, i.e.,

quote:

TABLE FILE AGEDATA_11_A
PRINT
JUL10
AUG10
BY SEX
BY _TYPE_
ETC.
END

The confounding aspect of this is that I cannot be that I will have just one sort field or multiple sort fields in other requests which use this data.

I have added the sort fieds like so:

quote:

TABLE FILE AGEDATA_11_A
PRINT
-*
-REPEAT :ENDREPEAT1 13 TIMES
-SET &COLNAME = EDIT(CHGDAT('YYM','MTY',&COLYRMTH,'A17'),'999$99');
&COLNAME.EVAL
-SET &COLYRMTH = AYM(&COLYRMTH,1,'I6YYM');
-IF (&COLYRMTH GT &RPTYRMTH) GOTO :EXITREPEAT1 ;
-:ENDREPEAT1
-:EXITREPEAT1
-*
BY SEX
BY _TYPE_
END

Thanks,
Tom