Sample Request: Pay Data For Noncitizen Employees

The sample report below provides year-to-date payment and withhold information for your noncitizen employees with visa type J or F.  In order to identify all your faculty, staff and student employees, a match is needed between the Active Current Status (AC) file and the Student (ST) file.  After that data is gathered and held, it is then matched to the Payment History (PH) file to get the pay data information.  


ssa          ph:wname        visa        ph:grosspay  pH:fedwh  ph:stewh

---          --------        ----        -----------  --------   -------

000-00-0001  fox, e j        j00n062005    $3,734.27       .00       .00

000-00-0002  penguin, h o    foon062007      $641.67     15.42       .00

000-00-0003  ANT, a s        jooN062005    $6,000.00   1107.00    361.74



The report request below is in the common library as FOC5003.  Note the following:

  • The verb SUM is used in the AC and ST files to get only one row of data for each employee.

  • The merge phrase OLD-OR-NEW is used for the AC and ST file to gather all data from either file that meets the selection criteria.  

  • The merge phrase OLD is used between the AC/ST data and the PH file to keep all data for noncitizen employees and incorporate any matching data from the PH file.  

  • If you only want to get pay data issued by your campus, include a screening statement for your campus (e.g., IF PH:CAMPX EQ _) in the portion of the request that is gathering data from the PH file.


EX ac

ex st

ex ph


match file ac

sum ac:noncitzn

by ac:ssa as 'ssa'

if ac:visa eq j or F



file st

sum st:noncitzn

by st:ssa as 'ssa'

if st:visa eq j or f

after match hold as permlrg old-or-new



file ph

sum ph:grosspay ph:fedwh ph:stewh

by ph:ssa as 'ssa'

by ph:wname

if ph:issuedte ge 2005/01/01

after match hold as permlrg old



define file permlrg

visa/a10 = if ac:noncitzn eq ' ' then st:noncitzn else ac:noncitzn;


table file permlrg

print ph:wname visa ph:grosspay ph:fedwh ph:stewh

by ssa

if ph:grosspay gt 0



For more information on defines and matching, refer to your FOCUS documentation.  For more information and instructions on accessing the common library refer to your CIRS User Manual.  


See also:  Matching More Than 2 Files