I've got a data file that contains records for students. Each record contains a student number, then can contain anywhere from one to several records of test results info for that same student. For example:

Stu TestType Date Test1 Test2 Test3 Test4 Test5 Test6
0001 AXX 01/01/07 89 87 91 93 24 76
0001 AXX 03/02/08 87 84 93 100 24 81
0001 AYY 04/02/08 430 321 121 321 430 21

In this scenario, I want to pick only the AXX test data, and within that data, pick only the highest results. Test AYY is to be ignored. I want the result to look like this:

0001 AXX 03/02/08 89 87 93 100 24 81

Next student:

0002 XYZ 06/08/08 35 40 76 92 43 67

This student result would look like the record going in.

Next student:

0003 ABC 07/03/06 43 44 32 56 82 (empty)
0003 DEF 07/03/06 42 44 32 56 100 22

This student result would look like this:

0003 DEF 07/03/06 42 44 32 56 100 22

I've tried using LAST - type logic (If STU_ID EQ LAST STU_ID..... but don't get the results I want. I still get more than one line of output when a student has more than one record of test data. Hers is the code I am using:

SCORE1A /P4 = IF ((STU_ID EQ LAST STU_ID) AND
(TEST_CODE EQ LAST TEST_CODE)
AND (SCORE1 GT LAST SCORE1)) THEN
SCORE1 ELSE LAST SCORE1;

Incidentally, I only want to use certain Tests... i.e., if TEST = XXX, then I want to ignore other tests, etc. and just to add to the fun, only to use the most recent dates when tests are identical.

I've got myself royally confused and would definitely appreciate anyone's assistance on something that is probably relatively simple. Thank you in advance, to all who reply.

This message has been edited. Last edited by: webmeister,

You are on the right track but why is AYY for student 1 ignored and DEF for student 3 not? That is confusing to me.

Webmeister,

How about:

  
TABLE FILE filename
WRITE MAX.DATE MAX.TEST1 MAX.TEST2 ... MAX.TEST6
BY STU
IF TEST EQ XXX
END

Hi, Ginny,

My bad... There are other criteria in this project that stipulate if the student has a test AXX, then just ignore test AYY... the same applies to student 3, but for other types of tests. It's somewhat like if the student has taken a test in Biology or whatever, then ignore the test that the student took in Phys Ed. Hope that helps explian a little more. I Perhaps this will help a little more:

If a student has test AXX and Test AYY, ignore test AYY
Within Test AXX, if the student has more than one test AXX, determine the most recent date of Test AXX
If there are multiple Tests of AXX, pick the latest date and also the highest test scores of all of the AXX tests.

Is that any clearer of an explanation? If not, would you please let me know?

Danny L.,

Thanks for your suggestion.... Not sure if that'll work, but I'll give it a try also. How is beautiful downtown Tel Aviv?

Thanks to you both for replying!

Well, I don't think that explains student 3.

It's the same concept, except in the case of a student having, say, tests ABC and DEF, the criteria given stipulate that test ABC and its results are to be discarded and that test DEF are to be used.

So, in the case of student 1, who has AXX, AXX and AYY, we're ignoring the AYY test, and in the case of student 3, who has tests ABC and DEF, we are ignoring ABC. Different tests, same concepts.

Does this work?

APP FI STUMAS DISK student.mas
-RUN
-WRITE STUMAS FILENAME=STUDENT,SUFFIX=FIX
-WRITE STUMAS SEGNAME=STUDENT, SEGTYPE=S0
-WRITE STUMAS FIELDNAME=STU,,FORMAT=A4,ACTUAL=A4,$
-WRITE STUMAS FIELDNAME=TESTTYPE,,FORMAT=A3,ACTUAL=A3,$
-WRITE STUMAS FIELDNAME=TESTDATE,,FORMAT=MDY,ACTUAL=A6MDY,$
-WRITE STUMAS FIELDNAME=TEST1,,FORMAT=I3,ACTUAL=A3,$
-WRITE STUMAS FIELDNAME=TEST2,,FORMAT=I3,ACTUAL=A3,$
-WRITE STUMAS FIELDNAME=TEST3,,FORMAT=I3,ACTUAL=A3,$
-WRITE STUMAS FIELDNAME=TEST4,,FORMAT=I3,ACTUAL=A3,$
-WRITE STUMAS FIELDNAME=TEST5,,FORMAT=I3,ACTUAL=A3,$
-WRITE STUMAS FIELDNAME=TEST6,,FORMAT=I3,ACTUAL=A3,$
APP FI STUDENT DISK student.ftm
-RUN
-WRITE STUDENT 0001AXX010107089087091093024076
-WRITE STUDENT 0001AXX030208087084093100024081
-WRITE STUDENT 0001AYY040208430321121321430021
-WRITE STUDENT 0002XYZ060808035040076092043067
-WRITE STUDENT 0003ABC070306043044032056082000
-WRITE STUDENT 0003DEF070306042044032056100022
TABLE FILE STUDENT
SUM TESTDATE MAX.TEST1 MAX.TEST2 MAX.TEST3 MAX.TEST4 MAX.TEST5 MAX.TEST6
BY STU
BY TESTTYPE
WHERE TESTTYPE NE 'AYY' OR 'ABC'
END

I'll copy your sample, stick my MVS/TSO JCL onto it and let you know as soon as I can.

But, and here's the stickler.... If there is not a test AXXX, then we have to include test AYY, and same thing for if there is not a test DEF then we have to use test ABC. It's this kind of crazy logic that is driving me nuts.

In the cases where a student has AXX and AYY, ignore AYY, but if a student only has AYY then use it. And of course, there are students who have test AXX, AYY, ABC and DEF so in those cases, we have to use AXX. It's going to be a fun mess, sorting through the multiple permutations!

Your max. will work unless the most current date is also needed. Sounds like a file with multiple tests and test types similar to the system I used to report on. Ended up doing selection for the max or most recent for a test type and then doing a merge on the information. Sorts by highest on the score wanted and then just including the date would get the information with a computed field based on last stu_id then do a where total test. Sorry I don't have access to post a code example any more. And I did get your private message as well.

Hi, Ginny,

In our MVS/TSO environment, I got the following errors, trying to run your sample:

FOCUS 7.1.1 10.12.31 08/21/2008 PDSLIST LINE 2 9622.04

FI STUMAS DISK STUDENT.MAS
0UNKNOWN FOCUS COMMAND FI
BYPASSING TO END OF COMMAND
0 ERROR AT OR NEAR LINE 4 IN PROCEDURE PDSLIST FOCEXEC
0(FOC340) DIALOGUE MANAGER -WRITE FILE NOT ALLOCATED OR FILEDEF'ED:
-WRITE STUMAS FILENAME=STUDENT,SUFFIX=FIX


Hi, Leah,

Interesting comments.... I'll digest them as much as possible.

Thank you all......

If you already have the file to report on, you don't need anything that I posted except the table request. I had to create test data.

And it's APP FI not FI. And if you are running this from mainframe FOCUS then you will have to use DYNAM ALLOC instead of doing a filedef. Remember, we all run on different platforms.

And Leah is correct. You could start off by not having the WHERE clause and take a look at what you get. Then post-process that file to do exclusions.

WM, you are going to have to go beyond what we tell you and do some experimenting on your own. You already know how to do the basics. Just print out your hold files and take a look at the contents and then think about what else you could do to isolate your problems.

Hi, all,

I'm beginning to get good results! Putting a little tweaking onto Danny L's suggestions was the piece that is working. And all others, thank you for your wonderful hints and advice - very appreciated.

Ginny, I think I'm good to go at the moment, and thank you for your time and patience.

Webmeister,

Tel Aviv is hot and damp... From this you can imagine that, although the office is in Tel Aviv, I live in Jerusalem where it is cool and dry in the evenings. Come and see!

Back to your students.
The MAX. prefix will always get you the highest value within the sort group, both the date and the grades.
Now you have to find the screening logic...

Just a thought, but isn't having multiple test scores in the same record causing some of this problem ? (Besides technically violating normalization ?)

Yes, it would require more records, one for each test instead of 6 scores per record, but data management would be more logical (programmatically) ?

I don’t know if it would solve the entire problem, but I hope this helps (imho) ?

I worked in the Sinai Desert back in 1981, in charge of computer operations for the Camp David work going on there. I've seen Tel Aviv and Jerusalem and would love to go back.

And now, back to the students issues.... I've got the initial issues solved and am getting the hightest scores coming out nicely, but here is the next part:

Students can have one or more tests, and of those tests, some of the tests have higher priority than others.

So, her is what is ultimately wanted -

Student 1
ACT Test <====== only this type of test is wanted on the output
ACCUP Test <====== discard this test, IF there is an ACT test present for the student

Student 2
SAT Test <===== only this type of test is wanted on the output

Student 3
ACT Test <====== only this type of test is wanted on the output
SAT Test <====== discard this test, IF there is an ACT test present for the student

Student 4
ACCUP Test <====== this test is wanted, since there is no ACT or SAT test
SISB1 Test <====== this test is also wanted
SISB2 Test <====== this test is also wanted

In short, if there is an ACT test for a student, take only that test and no others. If there is an SAT test but no ACT test, take only that test and no others. If there is no ACT or SAT test, then take all of the other test types.

Any ideas on how to work that kind of crazy logic?

Thanks for your help yesterday, and Shabbat Shalom!