I have a question that I’m hoping someone can help with. I’ve tried various functions but have yet found what I need to accomplish this.

Below is a sample street address as it is stored in our system.

123 MAIN ST APT 12

What I need to do is keep ‘123 MAIN ST’ in one field and create a second field with just ’12.’ I believe the define would start with:

ADDDRESS2/A5 = IF STREET_ADDR_1 CONTAINS ‘APT’ OR ‘UNIT’ OR ‘FL’ THEN….

I just don’t know how to keep everything before ‘Apt’ in field 1 and keep everything after ‘Apt’ in field 2. I tried TRIM and a few other functions but have not been successful.

I would truly appreciate any suggestions or help with this.

Look up CHKFMT and see if that can help.

Jackie,

You can use POSIT to find the position of APT in your string then use SUBSTR to extract the pieces. Here is an example:

DEFINE FILE CAR
ADDRESS/A25='123 MAIN ST APT 12';
APTLOC/I2=POSIT(ADDRESS,25,'APT',3,'I2');
ADDR1/A25=SUBSTR(25,ADDRESS,1,APTLOC-1,APTLOC,'A25');
APTNO/A5=SUBSTR(25,ADDRESS,APTLOC+3,25,25-APTLOC-3,'A5');
END
TABLE FILE CAR
PRINT COUNTRY ADDRESS APTLOC ADDR1 APTNO
IF RECORDLIMIT EQ 1
END  

Notice the liberal use of arithmetic when calculating start, end, and length parameters.

Ginny that looks good.

Jackie,

One more thing, if POSIT can't find the string APT or whatever you are looking for, then the APTLOC field would be zero. You can test on this before you do the SUBSTR. For instance,

FIELD/Ann=IF APTLOC EQ 0 THEN ' ' ELSE SUBSTR...

Give this a try:

DEFINE FILE CAR
STREET_ADDR_1/A100 = 
  IF COUNTRY EQ 'ENGLAND' THEN '123 MAIN ST UNIT 169' ELSE
  IF COUNTRY EQ 'ITALY'   THEN '3478 VIA APPIA FL 10' ELSE
  IF COUNTRY EQ 'FRANCE'  THEN '789 RUE ST. PAUL APT 1783' ELSE '392 HURON STREET';

NBR_LOC/I2 =
  IF STREET_ADDR_1 CONTAINS 'APT'  THEN POSIT(STREET_ADDR_1, 100, 'APT' , 3, 'I2') ELSE
  IF STREET_ADDR_1 CONTAINS 'UNIT' THEN POSIT(STREET_ADDR_1, 100, 'UNIT', 4, 'I2') ELSE
  IF STREET_ADDR_1 CONTAINS 'FL'   THEN POSIT(STREET_ADDR_1, 100, 'FL'  , 2, 'I2') ELSE 0;

STR_LEN/I2 = 
  IF STREET_ADDR_1 CONTAINS 'APT'  THEN 3 ELSE
  IF STREET_ADDR_1 CONTAINS 'UNIT' THEN 5 ELSE
  IF STREET_ADDR_1 CONTAINS 'FL'   THEN 2 ELSE 0;

APT_NBR/A6 = IF NBR_LOC GT 0 THEN SUBSTR(100, STREET_ADDR_1, NBR_LOC+STR_LEN, NBR_LOC+STR_LEN+6, 6, 'A6') ELSE '';
END
TABLE FILE CAR
PRINT
STREET_ADDR_1
NBR_LOC
APT_NBR
COUNTRY
END

As an alternative to Ginny's excellent suggestion, try using GETTOK like this -

APP FI ADDRESS DISK ADDRESS.MAS (LRECL 80
-RUN
-WRITE ADDRESS
-WRITE ADDRESS FILE=ADDRESS,SUFFIX=FOC
-WRITE ADDRESS SEGNAME=SEG1
-WRITE ADDRESS FIELD=ADDRESS_DATA,   ,A100   ,A100   , $
-RUN
CREATE FILE ADDRESS
MODIFY FILE ADDRESS
FREEFORM ADDRESS_DATA
DATA
123 MAIN ST APT 12,$
345 HIGH ST FL 123,$
678 EAST ST UNIT 19,$
1924 FLAPTUNIT SECTION 21,$
END
-RUN
DEFINE FILE ADDRESS
  NEW_ADDR/A100 = IF ADDRESS CONTAINS ' APT ' THEN STRREP(100, ADDRESS, 5, ' APT ', 1, '~', 100, NEW_ADDR)
             ELSE IF ADDRESS CONTAINS ' FL '  THEN STRREP(100, ADDRESS, 4, ' FL ', 1, '~', 100, NEW_ADDR)
             ELSE IF ADDRESS CONTAINS ' UNIT' THEN STRREP(100, ADDRESS, 6, ' UNIT ', 1, '~', 100, NEW_ADDR) 
             ELSE ADDRESS ;
  ADDR_PT1/A100 = GETTOK(NEW_ADDR, 100, 1, '~', 100, ADDR_PT1);
  ADDR_PT2/A100 = GETTOK(NEW_ADDR, 100, -1, '~', 100, ADDR_PT2);
END
TABLE FILE ADDRESS
PRINT ADDR_PT1
      ADDR_PT2
END

T

My suggestion would be the same as Tony's. This assumes that the apt/fl/unit number would be the last item in the field, but that would be a pretty safe assumption. Then, as Tony did, just parse the field into 1-everything BEFORE the last space(or ~ in Tony's case) and 2-everything AFTER the last space. If you're leaving as spaces, you would need to use TRIM to remove trailing spaces and format as AnV.

Thank you ALL!!! I was able to use a combination of these suggestions to make this work. I'm still fairly new to Focus and so I'm not certain what some of this coding did... but it worked!!!

Jackie

I'm glad your problem is solved, but it would be better if you try to find out how it is solved.
Tomorrow you will get an other question.

Try to lookup the defines in your manuals and to understand what is done.

DEFINE FILE CAR 
ADDRESS/A25='123 MAIN ST APT 12'; 
-* find in the string the place where the substring starts
APTLOC/I2=POSIT(ADDRESS,25,'APT',3,'I2');
-*select the substring that holds the first part.
ADDR1/A25=SUBSTR(25,ADDRESS,1,APTLOC-1,APTLOC,'A25');
-*select the substring that holds the second part.
APTNO/A5=SUBSTR(25,ADDRESS,APTLOC+3,25,25-APTLOC-3,'A5');
END
TABLE FILE CAR
PRINT COUNTRY ADDRESS APTLOC ADDR1 APTNO 
IF RECORDLIMIT EQ 1
END

What Tony and Francis did is in fact the same.

But they first created records because they did not have your dataset. (the write command Tony used did this)

... you'll also notice that I split the text using ' APT ' etc. e.g. a space either side of the target text. This is to ensure that incorrect parsing of the text is avoided - as in the fourth example data item.

T

To All -

I am so grateful for all your help! I absolutely will be researching each of the functions so I understand them better and can use them on future reports. This is a great forum and I truly appreciate the help you all offered so quickly.