We have a field with data in a "current" and "legacy" format. For the report, we want to create a defined field that translates the legacy code to a description that would be compatible with a description we have defined for the current code. The logic is if the first character is "^" upcarat then look at the description otherwise use the 2 characters beginning at position 5. I was able to get it to display the "current" code in a defined field by itself and to display the translated "legacy" code by itself, but when I tried to combine them in one field, I did not get the results I'm expecting. I imagine that the logic may be missing something. I am not too familiar with all the functions, so the define may not be written the best way. Here is an example of what the result should be:
--------------------------------
Code ==> Description
11S_ST_OD ==> ST
^05MMA MSEM_Manila ==> "MA"
--------------------------------
Using the above example, the actual output of the report shows:
CohortNew CohortOld CoCombined
ST XX ST
MA SF
------------------------
Line 2 CoCombined Should be MA not SF
------------------------

Here is the Define:
COHORTNEW/A3=IF BYTVAL(COHORT, 'I3') NE 94 THEN EDIT(COHORT, '$$$$99$$$$$');
POSITS/I3=BYTVAL(COHORT, 'I3');
COHORTOLD/A30=
IF GETTOK(COHORT_DESC, 100, -1, '_', 30, COHORTOLD) = 'Sacramento' THEN 'ST' ELSE
IF GETTOK(COHORT_DESC, 100, -1, '_', 30, COHORTOLD) = 'SF' THEN 'SF' ELSE
IF GETTOK(COHORT_DESC, 100, -1, '_', 30, COHORTOLD) = 'N Bay' THEN 'NB' ELSE
IF GETTOK(COHORT_DESC, 100, -1, '_', 30, COHORTOLD) = 'S Bay' THEN 'SB' ELSE
IF GETTOK(COHORT_DESC, 100, -1, '_', 30, COHORTOLD) = 'San Ramon' THEN 'SR' ELSE
IF GETTOK(COHORT_DESC, 100, -1, '_', 30, COHORTOLD) = 'LA' THEN 'LA' ELSE
IF GETTOK(COHORT_DESC, 100, -1, '_', 30, COHORTOLD) = 'Manila' THEN 'MA' ELSE
IF GETTOK(COHORT_DESC, 100, -1, '_', 30, COHORTOLD) = 'Bangkok' THEN 'BK' ELSE
IF GETTOK(COHORT_DESC, 100, -1, '_', 30, COHORTOLD) = 'Xiamen' THEN 'XI' ELSE
IF GETTOK(COHORT_DESC, 100, -1, '_', 30, COHORTOLD) = 'New Orleans Refugee' THEN 'NO' ELSE
'XX';
CoCombined/A3=
IF BYTVAL(COHORT, 'I3') NE 94 THEN EDIT(COHORT, '$$$$99$$$$$') ELSE
IF BYTVAL(COHORT, 'I3') EQ 94 THEN
IF GETTOK(COHORT_DESC, 100, -1, '_', 30, 'A3') = 'Sacramento' THEN 'ST' ELSE
IF GETTOK(COHORT_DESC, 100, -1, '_', 30, 'A3') = 'SF' THEN 'SF' ELSE
IF GETTOK(COHORT_DESC, 100, -1, '_', 30, 'A3') = 'N Bay' THEN 'NB' ELSE
IF GETTOK(COHORT_DESC, 100, -1, '_', 30, 'A3') = 'S Bay' THEN 'SB' ELSE
IF GETTOK(COHORT_DESC, 100, -1, '_', 30, 'A3') = 'San Ramon' THEN 'SR' ELSE
IF GETTOK(COHORT_DESC, 100, -1, '_', 30, 'A3') = 'LA' THEN 'LA' ELSE
IF GETTOK(COHORT_DESC, 100, -1, '_', 30, 'A3') = 'Manila' THEN 'MA' ELSE
IF GETTOK(COHORT_DESC, 100, -1, '_', 30, 'A3') = 'Bangkok' THEN 'BK' ELSE
IF GETTOK(COHORT_DESC, 100, -1, '_', 30, 'A3') = 'Xiamen' THEN 'XI' ELSE
IF GETTOK(COHORT_DESC, 100, -1, '_', 30, 'A3') = 'New Orleans Refugee' THEN 'NO';
END

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

You need an ELSE on the end of your defines and you caould simplify the code as follows.

COHORTNEW/A3=IF BYTVAL(COHORT, 'I3') NE 94 THEN EDIT(COHORT, '$$$$99$$$$$') ELSE '???';
POSITS/I3=BYTVAL(COHORT, 'I3');
TESTVAL/A3=GETTOK(COHORT_DESC, 100, -1, '_', 30, COHORTOLD);
COHORTOLD/A30=
IF TESTVAL EQ 'Sacramento' THEN 'ST' ELSE
IF TESTVAL EQ 'SF' THEN 'SF' ELSE
IF TESTVAL EQ 'N Bay' THEN 'NB' ELSE
IF TESTVAL EQ 'S Bay' THEN 'SB' ELSE
IF TESTVAL EQ 'San Ramon' THEN 'SR' ELSE
IF TESTVAL EQ 'LA' THEN 'LA' ELSE
IF TESTVAL EQ 'Manila' THEN 'MA' ELSE
IF TESTVAL EQ 'Bangkok' THEN 'BK' ELSE
IF TESTVAL EQ 'Xiamen' THEN 'XI' ELSE
IF TESTVAL EQ 'New Orleans Refugee' THEN 'NO' ELSE
'XX';
CoCombined/A3=
IF POSITS NE 94 THEN EDIT(COHORT, '$$$$99$$$$$') ELSE
-* this is redunadntIF BYTVAL(COHORT, 'I3') EQ 94 THEN
IF TESTVAL EQ 'Sacramento' THEN 'ST' ELSE
IF TESTVAL EQ 'SF' THEN 'SF' ELSE
IF TESTVAL EQ 'N Bay' THEN 'NB' ELSE
IF TESTVAL EQ 'S Bay' THEN 'SB' ELSE
IF TESTVAL EQ 'San Ramon' THEN 'SR' ELSE
IF TESTVAL EQ 'LA' THEN 'LA' ELSE
IF TESTVAL EQ 'Manila' THEN 'MA' ELSE
IF TESTVAL EQ 'Bangkok' THEN 'BK' ELSE
IF TESTVAL EQ 'Xiamen' THEN 'XI' ELSE
IF TESTVAL EQ 'New Orleans Refugee' THEN 'NO' ELSE '???' ;

Even better, you can rewrite those IF THEN ELSE statements to use DECODE, like so:

COHORTOLD/A30 = DECODE(
 'Sacramento' 'ST'
 'N Bay' 'NB'
 'S Bay' 'SB'
 'San Ramon' 'SR'
 ELSE 'XX'
);

I tried it using the following code:

EX -LINES 5 EDAPUT MASTER,COHORT,CV,FILE
FILENAME=COHORT, SUFFIX=FIX,$
SEGNAME=COHORT, $
  FIELD=COHORT        , ALIAS= ,A15  ,A15  , $
  FIELD=COHORT_DESC   , ALIAS= ,A100 ,A100 , $

EX -LINES 3 EDAPUT FOCTEMP,COHORT,CV,FILE
11S_ST_OD      SSSSSSSS
^05MMA         MSEM_Manila

FILEDEF COHORT DISK COHORT.FTM

DEFINE FILE COHORT
XYZZY/A30     = GETTOK(COHORT_DESC, 100, -1, '_', 30, XYZZY);
CoCombined/A3 = IF EDIT(COHORT, '9') NE '^' THEN EDIT(COHORT, '$$$$99$$$$$') ELSE 
                DECODE XYZZY('Sacramento' 'ST' 'SF' 'SF' 'N Bay' 'NB' 'S Bay' 'SB' 'San Ramon' 'SR'
                             'LA' 'LA' 'Manila' 'MA' 'Bangkok' 'BK' 'Xiamen' 'XI' 'New Orleans Refugee' 'NO'
                ELSE 'XX');
END

TABLE FILE COHORT
PRINT COHORT COHORT_DESC CoCombined
END

and it gave me the required result.
Hope this helps ...

This looks way too complicated for what you are trying to do. For starters, why not take a look at the DECODE function to get rid of all of those IF THEN ELSE statements. You can then use the EDIT function to check for the upcaret and also to select the 5th and 6th characters.

Oops .... looks like Wep5622 and GamP beat me to it ....

Thank you all for your great help. I used GamP's code and it is working. This brought to light that there are some more "exceptions". I noticed a case where it did not translate a "San Ramon". I thought perhaps it had to do with the "^" have an "_" in the description, meaning that it picks up "_San Ramon", but not picking up " San Ramon". However, "New Orleans" does not have an "_" and it is picked up, so now I'm not sure what to think.

COHORT COHORT_DESC COHORTNEW COHORTOLD CoCombined
^NOLA New Orleans Refugee NO NO
^08SSR Clinic Nurse Lead San Ramon SR XX XX
^5FRAC EDUC_San Ramon SF SR SR

I overcame the issue by adding hard code values in order to get the description to show up. I tried to spell out the wording in the decode, but that didn't work, so I used an IF for the San Ramon. I left the wording in the decode in the snippet below, however, in case I missed something.

 
[B]IF COHORT = '^08SSR' THEN 'SR' ELSE [/B]
DECODE XYZZY('Sacramento' 'ST' 'SF' 'SF' 'N Bay' 'NB' 'S Bay' 'SB' 'San Ramon' 'SR' 'Clinic Nurse Lead San Ramon' 'SR'
'LA' 'LA' 'Manila' 'MA' 'Bangkok' 'BK' 'Xiamen' 'XI' 'Korea Inst' 'KO' 'New Orleans Refugee' 'NO'
ELSE 'XX');
CoCombinedDesc/A30=
DECODE CoCombined('ST' 'Sacramento' 'SF' 'San Francisco' 'GH' 'SF General Hospital' 'NB' 'North Bay' 'SB' 'South Bay'
'SR' 'San Ramon' 'PA' 'Palo Alto' 'FR' 'Fresno' 'LA' 'Los Angeles' 'RA' 'SF Prof Study Adv and Support' 'MA' 'Manila' 'BK' 'Bangkok'
'XI' 'Xiamen' 'KO' 'Korea' 'MY' 'Malaysia' 'NO' 'New Orleans' ELSE 'XX'); 

A footnote:

For readbility you can arrange your DECODE vertically, or even put the decode pairs in a separate file. You could create that file dynamically as well.

eg:

DECODE XYZZY(
'Sacramento' 'ST' 
'SF'         'SF' 
'N Bay'      'NB' 
'S Bay'      'SB' 
'San Ramon'  'SR' 
...ETC
ELSE 'XX');