TANF-ACF-IM-2009-02 (SAS Programs to calculate the monthly work participation rates from the State’s quarterly transmission files for FY 2009)

Publication Date: October 19, 2009
Current as of:

TO:

State agencies administering the Temporary Assistance for Needy Families (TANF) Program and other interested parties.

SUBJECT:

SAS Programs to calculate the monthly work participation rates from the State’s quarterly transmission files for FY 2009

BACKGROUND:

Section 407 of the Social Security Act (the Act) requires a State operating a TANF program to meet minimum annual work participation rate standards.  The Act specifies separate minimum work participation rates for all families and two-parent families for a State. 

The Deficit Reduction Act of 2005 (DRA) revised the work participation  rates in section 407 of the Act to include not just families receiving TANF assistance but also families receiving assistance in separate State programs for which the State claims MOE expenditures (SSP-MOE programs) in the official overall and two-parent work participation rates.

To achieve these annual work participation rate standards, State should monitor their work participation rates on a monthly basis.  It is helpful for States to know in detail how the Department calculates their monthly work participation rates and how the Department determines data inconsistencies and erroneous data used in this process.  It is for these reasons that we are making the SAS code available to the States.

CONTENT:

This memorandum informs States that they can download from our website the Statistical Analysis System (SAS) programs and documentation files that will enable a State to calculate its monthly work participation rates from its quarterly transmission files. 

The calculation used in the SAS code is the same as used by the Department to calculate the State’s official monthly work participation rates.  The official, annual work participation rate is a simple average of the twelve monthly work participation rates. 

The documentation file, TRANS_WPR_README.DOC, describes the system requirements for running the SAS-PC programs, the instructions for setting up the files and folders that the programs use and the instructions for running the programs. 
There are two SAS programs, TRANS_TANWPRP1.SAS and TRANS_TANWPRP2.SAS, that generate the work participation rates and related files for the State TANF program.  There are two SAS programs, TRANS_SSPWPRP1.SAS and TRANS_SSPWPRP2.SAS, that generate the work participation rates and related files for separate State programs for which the State claims MOE expenditures.  There is one SAS program, TRANS_CMBWPR.SAS, that generates the combined work participation rates.

The TRANS_TANWPRP1.SAS program reads the quarterly TANF transmission file for active cases, the quarterly TANF weight file, writes out a monthly file of counters for active TANF cases, a monthly TANF error flag file, and then sums and weights the data and writes out a monthly TANF summed weighted SAS dataset.  The TRANS_SSPWPRP1.SAS program does the same for the SSP-MOE data. 

TheTRANS_TANWPRP2.SAS program reads the three monthly TANF summed and weight files, calculates the monthly TANF work participation rates (including any adjustments for exceeding the thirty percent limit), and writes out two files.  The first file is a text file with the TANF summary data for the work participation rates and the second file is a text file with the TANF work participation rates.  The TRANS_SSPWPRP2.SAS program does the same for the separate State programs and the TRANS_CMBWPR.SAS program calculates the combined work participation rates.

FILES to DOWNLOAD:

One documentation file (TRANS_WPR_README) and five SAS program zip files

INQUIRIES:

Please direct any inquiries to the TANF Program Manager in your Region.

 

/s/

Ann H. Barbagallo
Office of Family Assistance

Instructions for Computing the Work Participation Rates from the Quarterly Transmission Files

Purpose

The purpose of this document is to explain how to generate the monthly work participation rates from the quarterly transmission files for active cases. The process uses five SAS-PC programs. Two programs, TRANS_TANWPRP1.SAS and TRANS_TANWPRP2.SAS, are used to generate the TANF monthly work participation rates. Two programs, TRANS_SSPWPRP1.SAS and TRANS_SSPWPRP2.SAS, are used to generate the SSP-MOE monthly work participation rates. The fifth program, TRANS_CMBWPR, produces the combine monthly work participation rates and must be run after running TRANS_TANWPRP1.SAS and TRANS_SSPWPRP1.SAS programs. For a State that does not have a separate State program for which it claims MOE, the State needs only the first two programs.


System Requirements

To run the SAS programs, a State will need a version of SAS-PC running on an IBM compatible pc. The programs were written and tested using SAS-PC version 8.2. They are written using very basic code and should work with any version of SAS-PC. The programs can be modified to run on a mainframe computer.

Setup

  1. On a personal computer that has the SAS-PC software package, Create a subdirectory of \My Documents named \TRANSMISSION.
  2. Create a subdirectory of \TRANSMISSION named \FY2009. Each new fiscal year add a new subdirectory (\FY2010, \FY2011, etc.).
  3. Copy the quarterly TANF and SSP-MOE transmission files for FY 2009 active case data to the \TRANSMISSION\FY2009 subdirectory.
  4. Create a quarterly TANF weight file (and, if applicable, a quarterly SSP-MOE weight file) and save the file in the \TRANSMISSION\FY2009 subdirectory. The name of the TANF weight file should be: WGT20091_TAN.TXT for FQ 2009-1, WGT20092_TAN.TXT for FQ 2009-2, etc. The name of the SSP-MOE weight is WGT20091_SSP.TXT. The weight file can be created using any standard text editor such as WordPad. The weight file must contain one line for each stratum used in TDR - Section one data (or, for the SSP-MOE weights, in the SDR- Section one data) for the quarter. The format of each line is as follows:
    - Columns 1 - 2 STATE FIPS Code
    - Columns 3 - 4 Blank
    - Columns 5 — 6 Stratum Code
    - Columns 7 - 8 Blank
    - Columns 9 - 17 Stratum Caseload for first month of the quarter
    - Columns 18 — 19 Blank
    - Columns 20 — 28 Stratum Caseload for the second month of the quarter
    - Columns 29 — 30 Blank
    - Columns 31 — 39 Stratum Caseload for the third month of the quarter
    - Column 40 Lime Ending Character, such as a Y.
  5. For a State that samples but does not stratify its sample or for a State that reports its entire caseload, each case in Section one should be assigned the same stratum code. Thus, the weight file will consist of one line and the monthly caseload data as reported in Section three will be used as the monthly caseload in the weight file.
  6. For each of the five programs ( TRANS_WPRP1.SAS, TRANS_WPRP2.SAS, TRANS_SSPWPRP1.SAS, TRANS_SSPWPRP2.SAS, and TRANS_CMBWPR.SAS), modify the LIBNAME and FILENAME statements to reflect the path on your computer for the appropriate folder. That is, replace the c:\DOCUMENTS AND SETTINGS\PATRICK BRANNEN with whatever your pc path is.

TRANS_TANWPRP1.SAS

This program reads the quarterly TANF transmission file for active cases, the quarterly TANF weight file, writes out a monthly file of counters for active TANF cases, a monthly TANF error flag file, and then sums and weights the data and writes out a monthly TANF summed weighted SAS dataset. This program is run three times for each quarter (i.e., once for each month in the quarter). You can submit the run for all months at the same time by adding a line of code to execute the program for the additional months. See the example below.

The input files are the quarterly transmission file and the weight file (e.g., WGT20091_TAN.TXT).

The output files are the monthly file of counters (e.g., TN200810.P5B) for active TANF cases, the monthly TANF error flag file (e.g., TANF45_EF0810.TXT would be the file name for South Carolina’s October 2008 TANF error flag file) and the monthly summed weighted data file (e.g., sf0810). The monthly error flag file has the same format as the quarterly error flag file sent to States each month.

The macro and parameters used in the macro that executes the program are as follows:
%macro createm(TRNFLE,FIP,FY,YYM,QTR,YQ,MON) ;
TRNFLE — Name of the Transmission File 
FIP - TWO DIGIT STATE FIPS CODE 
FY - FISCAL YEAR 
YYM - 6 DIGIT CALENDAR YEAR AND MONTH 
QTR - 2 DIGIT FISCAL YEAR AND 1 DIGIT QUARTER
YQ - 4 DIGIT FISCAL YEAR AND 1 DIGIT QUARTER 
MON - 2 DIGIT FISCAL YEAR AND 2 DIGIT MONTH

The macro that appears at the bottom of the program might look like the following for South Carolina for FQ 2009-1 quarterly run.

%createm(TANQTR1.txt,45,2009,200810,091,20091,0810) ;
%createm(TANQTR1.txt,45,2009,200811,091,20091,0811) ;
%createm(TANQTR1.txt,45,2009,200812,091,20091,0812) ;

In the above example, there are three lines of code; one for each month of the quarter. In the first line, the name of the transmission file is: TANQTR1.TXT. The State FIPS code is 45. The fiscal year is 2009. The calendar year and month for the first macro is 200810. The three digit quarter is 091. The 5 digit fiscal quarter is 20091. And, the 4 digit month is 0810.

All output files for FQ 2009-1, FQ 2009-2, FQ 2009-3, and FQ 2009-4 are written to the \FY2009 subdirectory.

To run for a different quarter or for a different State simply modify the parameters in the %createm macro at the bottom of the program accordingly.

TRANS_TANWPRP2.SAS

This program reads the three monthly TANF summed and weight files, calculates the monthly TANF work participation rates (including any adjustments for exceeding the thirty percent limit), and writes out two files. The first file is a text file with the TANF summary data for the work participation rates and the second file is a text file with the TANF work participation rates. 

The macro and parameters used in the macro that executes the program are as follows:

%macro createm(STNAME,FY,LY,YY,MONA,MONB,MONC,Q) ; STNAME: State Name, 
FY : current fiscal year, format YYYY 
LY : current calendar year, format YYYY 
YY : CALENDAR year, format YY 
MONA : first month, format MM
MONB : second month, format MM
MONC : third month, format MM
Q : QUARTER DESIGNATION format Q 

The macro that appears at the bottom of the program might look like the first line of the following for South Carolina for FQ 2009-1 quarterly run. The other three lines are for the remaining three quarters of FY 2009.

%createm("SOUTH CAROLINA",2009,2008,08,10,11,12,1) ;
%createm("SOUTH CAROLINA",2009,2009,09,01,02,03,2) ;
%createm("SOUTH CAROLINA",2009,2009,09,04,05,06,3) ;
%createm("SOUTH CAROLINA",2009,2009,09,07,08,09,4) ;

TRANS_SSPWPRP1.SAS

This program reads the quarterly SSP transmission file for active cases and the quarterly SSP weight file , writes out a monthly file of counters for SSP active cases, a monthly SSP error flag file, and then sums and weights the data and writes out a monthly SSP summed weighted file. This program is run three times for each quarter (i.e., once for each month in the quarter). You can submit the run for all months at the same time by adding a line of code to execute the program for the additional months. 

The input files are the quarterly SSP-MOE transmission file for active cases, the SSP-Moe quarterly weight file (e.g., WGT20091_SSP.TXT for FQ 2009-1).

The output files are the monthly file of counters for active SSP cases (e.g., SP200810.P5B), the monthly SSP error flag file (e.g., SSPF45_EF0810.TXT) and the monthly summed weighted SSP data file (e.g., sm0810). The monthly SSP error flag file has the same format as the quarterly error flag file sent to States each month.

The macro and parameters used in the macro that executes the program are as follows:

%macro createm(TRNFLE,FIP,FY,YYM,QTR,YQ,MON) ;
TRNFLE — Name of the Transmission File 
FIP - TWO DIGIT STATE FIPS CODE 
FY - FISCAL YEAR 
YYM - 6 DIGIT CALENDAR YEAR AND MONTH 
QTR - 2 DIGIT FISCAL YEAR AND 1 DIGIT QUARTER
YQ - 4 DIGIT FISCAL YEAR AND 1 DIGIT QUARTER 
MON - 2 DIGIT FISCAL YEAR AND 2 DIGIT MONTH

The macro that appears at the bottom of the program might look like the following for South Carolina for FQ 2009-1 quarterly run.
%createm(SSPQTR1.txt,45,2009,200810,091,20091,0810) ;
%createm(SSPQTR1.txt,45,2009,200811,091,20091,0811) ;
%createm(SSPQTR1.txt,45,2009,200812,091,20091,0812) ;

All output files For FQ 2009-1, FQ 2009-2, FQ 2009-3, and FQ 2009-4 are written to the \FY2009 subdirectory.

To run for a different quarter or for a different State, simply modify the parameters in the macro at the bottom of the program accordingly.

TRANS_SSPWPRP2.SAS

This program reads the three monthly SSP summed and weight files, calculates the monthly SSP work participation rates (including any adjustments for exceeding the thirty percent limit), and writes out two files. The first file is a text file with the SSP summary data for the work participation rates and the second file is a text file with the SSP work participation rates. 

The macro and parameters used in the macro that executes the program are as follows:
%macro createm(STNAME,FY,LY,YY,MONA,MONB,MONC,Q) ; STNAME: State Name, 
FY : current fiscal year, format YYYY 
LY : current calendar year, format YYYY 
YY : CALENDAR year, format YY 
MONA : first month, format MM
MONB : second month, format MM
MONC : third month, format MM
Q : QUARTER DESIGNATION format Q

The macro that appears at the bottom of the program might look like the first line of the following for South Carolina for FQ 2009-1 quarterly run. The other three lines would execute the program for the remaining three quarters of FY 2009.
%createm("SOUTH CAROLINA",2009,2008,08,10,11,12,1) ;
%createm("SOUTH CAROLINA",2009,2009,09,01,02,03,2) ;
%createm("SOUTH CAROLINA",2009,2009,09,04,05,06,3) ;
%createm("SOUTH CAROLINA",2009,2009,09,07,08,09,4) ;

TRANS_CMBWPR.SAS

This program reads the three monthly TANF summed and weight files and the three monthly SSP summed and weighted files, calculates the monthly combine TANF and SSP work participation rates (including any adjustments for exceeding the thirty percent limit), and writes out a text file with the combined TANF and SSP work participation rates. 

The macro and parameters used in the macro that executes the program are as follows:
%macro createm(STNAME,FY,LY,YY,MONA,MONB,MONC,Q) ; STNAME: State Name, 
FY : current fiscal year, format YYYY 
LY : current calendar year, format YYYY 
YY : CALENDAR year, format YY 
MONA : first month, format MM
MONB : second month, format MM
MONC : third month, format MM
Q : QUARTER DESIGNATION format Q 

The macro that appears at the bottom of the program might look like the first line of the following for South Carolina for FQ 2009-1 quarterly run. The other three line would execute the program for the remaining quarters of FY 2009.
%createm("SOUTH CAROLINA",2009,2008,08,10,11,12,1) ;
%createm("SOUTH CAROLINA",2009,2009,09,01,02,03,2) ;
%createm("SOUTH CAROLINA",2009,2009,09,04,05,06,3) ;
%createm("SOUTH CAROLINA",2009,2009,09,07,08,09,4) ;

Files