Enterprise
Reporting Services

  Temple.edu     Admissions     Schools and Campuses     About Temple     Directories     Search Temple  
Enterprise Reporting Services
Contact Us
Cognos Reporting
WebFocus
Search ERS
Computer Services
Confidentiality of Student Records

Combining Files with Concatenation
Last Updated: 1/1/2007

 Introduction

It is possible to set up multiple data files and combine them so that they appear to be a single file to Focus. Conceptually, you append each file to the other.

To take advantage of this technique, each of the files must be exactly the same. That is, all of the fields must have the same names and the structure of the files must be the same.

This technique is especially useful when you are looking at data from files that differ from each other only because they reflect different time periods or, as is the case with payroll files, because they reflect different organizational units.

If you are an ISIS user, you may know that the Section files exist for each of the semesters. The type 1 record shows information on the section including how many students registered in each section. If you wanted to find out how many students registered for each section across an entire year (all four semester), you could use the concatenation technique.


 General Steps

Concatenating files involves the following general steps:

  • First, extract the information that you want from each of the files and hold the data in HOLD files. Be sure that you extract the exact same fields from each file.
  • Next, concatenate the HOLD files using the DYNAM CONCAT FILE command.
  • Create your report.



Example

The following Focus program extracts information from each of the Section files, concatenates the files (see line 00029), and gives a report of the number of registrations by Course Reference Number for the entire year.

00001 DYNAM ALLOC FILE SECTTYP1 DA PV0102.A143200.P87360.SUM1.TYPE1 SHR REUS00002 TABLE FILE SECTTYP100003 PRINT00004     SEMESTER_YR SEMESTER_WK K_CRS_REF_NO TOT_REGD00005 ON TABLE HOLD AS HOLD100006 END0000700008 DYNAM ALLOC FILE SECTTYP1 DA PV0102.A143200.P87360.SUM2.TYPE1 SHR REUS00009 TABLE FILE SECTTYP100010 PRINT00011     SEMESTER_YR SEMESTER_WK K_CRS_REF_NO TOT_REGD00012 ON TABLE HOLD AS HOLD200013 END0001400015 DYNAM ALLOC FILE SECTTYP1 DA PV0102.A143200.P87360.FALL.TYPE1 SHR REUS00016 TABLE FILE SECTTYP100017 PRINT00018     SEMESTER_YR SEMESTER_WK K_CRS_REF_NO TOT_REGD00019 ON TABLE HOLD AS HOLD300020 END0002100022 DYNAM ALLOC FILE SECTTYP1 DA PV0102.A143200.P87360.SPRING.TYPE1 SHR REUS00023 TABLE FILE SECTTYP100024 PRINT00025     SEMESTER_YR SEMESTER_WK K_CRS_REF_NO TOT_REGD00026 ON TABLE HOLD AS HOLD400027 END0002800029 DYNAM CONCAT FILE HOLD1 HOLD2 HOLD3 HOLD40003000031 TABLE FILE HOLD100032 SUM TOT_REGD00033 BY K_CRS_REF_NO00034 END0003500036 DYNAM FREE FILE HOLD1

The group of concatenated files is referred to as HOLD1. When you write the command to DYNAM CONCAT FILE, the name of the first file that is in the concatenation list is given to the concatenated group. In this case, the group of files is referred to as HOLD1 in line 00031 since this was the first file in the concatenation list (see line 00029).

TRAP #1: YOU MUST ALWAYS FREE ANY CONCATENATED FILES AT THE END OF YOUR PROGRAM. THE SINGLE MOST IMPORTANT LINE IN THIS PROGRAM IS LINE 00036. This line frees the HOLD1 file after the program is executed. When you run the program again, the HOLD1 file will be concatenated correctly with the other hold files. If you did not include line 00041, HOLD1 would remain allocated and all of the old hold files would remain concatenated to it. The reallocation of the new hold files to HOLD1 will disrupt the data and you will get erroneous and strange results.

The report would look something like the following:

Page 1K_CRS_REF_NO     TOT_REGD------------  ------------012345              35013333             130014445             201...ETC....

 Using Concatenation to do Historical Reporting

You may be saying, "Wow. Using CONCAT to add up all of the registrations is really cool but I want to see the historical trend of registrations for each of the sections in the fall semesters over the past 4 years."

This can be done. First, however, you will have to extract the historical tape data using the ISISTAPE program. In ISISTAPE, use option 1, "COPY SECTION TAPE DATA TO PERSONAL DISK FILE", and the select the END cycle and RECORD TYPE 1. Run this program for each of the years that you want to include in your report.

Assuming that you have done that and you now have the fall data available on disk, the following program will display the data you seek:

00001 DYNAM ALLOC FILE TAPETYP1 DA UN0100.U772405.END9736.TYPE1 SHR REUS00002 TABLE FILE TAPETYP100003 PRINT00004     SEMESTER_YR SEMESTER_WK K_CRS_REF_NO TOT_REGD00005 WHERE TOT_REGD GT '0000'00006 ON TABLE HOLD AS HOLD100007 END0000800009 DYNAM ALLOC FILE TAPETYP1 DA UN0100.U772405.END9636.TYPE1 SHR REUS00010 TABLE FILE TAPETYP100011 PRINT00012     SEMESTER_YR SEMESTER_WK K_CRS_REF_NO TOT_REGD00013 WHERE TOT_REGD GT '0000'00014 ON TABLE HOLD AS HOLD200015 END0001600017 DYNAM ALLOC FILE TAPETYP1 DA UN0100.U772405.END9536.TYPE1 SHR REUS00018 TABLE FILE TAPETYP100019 PRINT00020     SEMESTER_YR SEMESTER_WK K_CRS_REF_NO TOT_REGD00021 WHERE TOT_REGD GT '0000'00022 ON TABLE HOLD AS HOLD300023 END0002400025 DYNAM ALLOC FILE TAPETYP1 DA UN0100.U772405.END9436.TYPE1 SHR REUS00026 TABLE FILE TAPETYP100027 PRINT00028     SEMESTER_YR SEMESTER_WK K_CRS_REF_NO TOT_REGD00029 WHERE TOT_REGD GT '0000'00030 ON TABLE HOLD AS HOLD400031 END0003200033 DYNAM CONCAT FILE HOLD1 HOLD2 HOLD3 HOLD40003400035 TABLE FILE HOLD100036 SUM TOT_REGD00037 BY K_CRS_REF_NO00038 ACROSS SEMESTER_YR00039 END0004000041 DYNAM FREE FILE HOLD1

The report would look something like the following:

Page 1               SEMESTER_YRK_CRS_REF_NO    97           96          95           94------------  ------------------------------------------012345            0035                  0023        0034013333            0130      0111        0021014445            0201      0021        0035...ETC....

There are a number of tricks and traps in this program:

TRAP #1: YOU MUST ALWAYS FREE ANY CONCATENATED FILES AT THE END OF YOUR PROGRAM. THE SINGLE MOST IMPORTANT LINE IN THIS PROGRAM IS LINE 00041. This line frees the HOLD1 file after the program is executed. When you run the program again, the HOLD1 file will be concatenated correctly with the other hold files. If you did not include line 00041, HOLD1 would remain allocated and all of the old hold files would remain concatenated to it. The reallocation of the new hold files to HOLD1 will disrupt the data and you will get erroneous and strange results.

TRAP #2: Note that, in lines 00001, 00009, 00017 and 00025, you will have to substitute the name of the extract file that you created with ISISTAPE.

TRICK #1: SUM is used on line 00036 instead of PRINT. If you use PRINT, each semester's numbers will be printed on a separate line. Using SUM collapses the individual lines into a single line.

TRICK #2: In each of the table sections of the program, we use WHERE TOT_REGD GT '0000'. When using the Section file and the Type 1 record, you need to know that a record exists for each CRN (Course Reference Number) even if there are no registrations in that course. We eliminate courses with no registrations using the WHERE statement.








Temple University, Computer Services, Philadelphia, PA 19122