Creating a 'Unique' Key Field in Systemwide Files

CIRS offers 3 files that contain employment data for all active and onleave faculty, staff and management positions within the CSU system. These files have position data and some personal data, but do not have confidential data such as SSA, name and address. The systemwide files are:

Systemwide Active Current Status File (SAC) - weekly snapshot as of the previous Friday
Systemwide Annual Current Status File (SAN) - annual snapshot as of 10/31
Systemwide Transaction Data File (STR) - historical data back to 1976

Generally, reports generated from systemwide files contain summarized data at the position level. For example, a count of positions by class code. But sometimes there's a need for information at the employee level. For example, a head count of employees by class code. That type of report represents a problem because employee counts are generally obtained using the field SSA, which is not available in the systemwide files. However, there are some ad hoc reporting techniques you could use to get estimated head counts. The example that follows uses the SAC file, but it can be used with the SAN or the STR files as well. The entire report is available in the common library as FOC6001.

Step 1: Define A Key Field

Begin by concatenating all the real (non-defined) fields in the first segment of the file into a single key field. Concatenation can only be used with alpha fields, so you must first convert birth date from a smart date field (SAC:BRTHDATE) to an alpha field (SAC:BIRTH) and then concatenate it to the other fields. For example:

EX SAC

DEFINE FILE SAC ADD

SAC:BIRTH/A8MDYY = SAC:BRTHDATE;

SAC:KEY/A15 = (SAC:BIRTH|SAC:SEX|SAC:ETHNIC|SAC:DISABLE);

END

Step 2: Create A Hold File Using List

The next step is to write a report that lists all the fields required for the final report, and place the data in a hold file (e.g., PERMRPT). By using the verb LIST, each row of data in the resulting hold file will be numbered. The row number will be used in the next step. Do not include any sorts in the report. For example:

TABLE FILE SAC

LIST SAC:KEY SAC:CAMPUS SAC:CLASS

ON TABLE HOLD AS PERMRPT

END

Step 3: Define A Unique Identifier

Using the list and key data in the hold file, define a new field to be used as a (somewhat unique) identifier in place of SSA for generating a head count.

DEFINE FILE PERMRPT

IDENTIFIER/I5 = IF (SAC:KEY EQ LAST SAC:KEY) THEN LAST LIST ELSE LIST;

END

Step 4: Generate Report

The final report provides an estimated head count of employees by campus and class code because it doesn't create a truly unique key. For comparison, the actual head count of all active/onleave employees, by campus, as of December 30, 2005 is 52,243. The report below generates a count of 54,283.

TABLE FILE PERMRPT

COUNT DST.IDENTIFIER

BY SAC:CAMPUS

BY SAC:CLASS

END

If you want to know more about defines and hold files, refer to your FOCUS documentation. Also, don't forget about the multitude of reports with systemwide data that are available for printing, downloading or even editing in the CIRS compendium. These reports do not incur any CPU charges and are updated on a regular basis.

For complete information about the systemwide files, refer to the CIRS Data Element Dictionary.