I know there is an easy way to do what I want but at the moment it is escaping me. I have two files I need to read in. One is a valid Zip code file, the other is a list of addresses (a one to many relationship). Anyway, I need to read in the list of addresses and if the zip code matches a zip code on the valid zip code list, I need to set a flag to 0. If it does not match I need to set the flag to 1. Can I do a join and force it not to drop records? Should I do a match? Please help.


Thanks in advance for your help!!!!

According to your posting:

"Anyway, I need to read in the list of addresses and if the zip code matches a zip code on the valid zip code list, I need to set a flag to 0. "

Firstly, does the valid zip code file contain all of the valid zip codes? From what you've posted, it appears that you may have a zip code in the address list that may not appear in the valid zip code list. Is this correct? If so, then why does the address list contain these invalid zips? It must have something to do with the greater picture.

Basically, a join from the address file to the valid zip code list should work for you. If the zip code is not found in the valid zip code table, any data from that file will be null after the join. Don't do the join the other way.

Of course, this is a best guess based on the information you've provided.

Ken

You can also add:

SET ALL = ON

Good Luck
Carol

I have tried/combined both the above ideas however my output is still not right. The join Ken suggested along with using SET ALL = ON gives me all the records, however the Zip Flag is 0 only for the first occurance of that Zip and 1 for all others which is incorrect. Below is a short example of what I have as input, the output I am getting, and the desired output.

Input:

Valid Zip code list
------------------
99901
99902
99903
99914

Address List zip codes
-----------------
99901
99901
99901
99901
99902
99902
99905
99905
99905
99914
99914

Current output:

Address Zip Zip Flag
------------------------
99901 0
99901 1
99901 1
99901 1
99902 0
99902 1
99905 1
99905 1
99905 1
99914 0
99914 1

Desired output:

Address Zip Zip Flag
------------------------
99901 0
99901 0
99901 0
99901 0
99902 0
99902 0
99905 1
99905 1
99905 1
99914 0
99914 0

From your symptoms, I gather these are flat HOLD files (SUFFIX=FIX), both sorted on zip code.

Table processing with a join from Address file to Zip file reads forward in both files after processing a matched pair. That's why the subsequent Address records for a given zip are not matched.

To associate each Address with the corresponding Zip-file reference record, you need a one-to-many join, in the opposite direction:

JOIN zip IN Zips TO ALL zip IN Addresses

I tried doing the join the other way. However I only get records that match in the join. Therefore any aip code on the address file that does not have a match on the valid zip code list is dropped (which I cannot have). If I use SET ALL = ON, I get a bunch of Null records for the Zips on the valid zip code list that I did not use.

I need the input to match the output with exception of the new valid zip flag being set to 0 or 1 (correctly!).

Hold the zip reference file as a focus file, with the zip key indexed,

ON TABLE HOLD AS zipfile FORMAT FOCUS INDEX zipkey

and join from the address file to it (without ALL).

It worked! I finally got it. Thanks to all for all your help.

Focus can be frustrating. However I know it is powerful and just need more experience with it. It will come but in the meantime, this a great resource!

Thanks again!