I have created a hold file that has a single column of data. I want to turn that column into an amper variable where each row is separated by a comma.

For Example:

 
TABLE FILE CAR
SUM
MAX.COUNTRY AS COUNTRY
BY COUNTRY NOPRINT
ON TABLE HOLD AS HCOUNTRY FORMAT FOCUS INDEX COUNTRY
END 

Output:
COUNTRY
ALBANIA
CANADA
CUBA
E GERMANY
ENGLAND
FRANCE
GREECE
IRAN
IRAQ
ITALY
JAPAN
N KOREA
RUSSIA
S KOREA
W GERMANY

I want to make an amper variable from this list that looks like.

&COUNTRY = ALBANIA, CANADA, CUBA, E GERMANY, ENGLAND, FRANCE, GREECE.....

Bethany

WHERE COUNTRY EQ '&COUNTRY.(FIND COUNTRY IN HCOUNTRY ).Country';

quote:

'&COUNTRY.(FIND COUNTRY IN HCOUNTRY ).Country'

Can this be used in an IF statement?

Bethany

Here's one way:

  TABLE FILE CAR
    BY COUNTRY
    ON TABLE HOLD
  END
-RUN
-SET &N=&LINES;
-SET &NN=12*&N;
  DEFINE FILE HOLD
    I/I2=I+1;
    STRINGV/A&NN|V = IF (I EQ 1) THEN COUNTRY ELSE STRING || (', '| COUNTRY);
    STRING /A&NN   = STRINGV;
  END
  TABLE FILE HOLD
    WRITE LST.STRING
    ON TABLE SAVE
  END  
-RUN
-READ SAVE &STRING.A&NN.EVAL
-? &STRING

Would it work the same if instead of countries you had a list of numbers (P7 format)?

You'd need to convert the values to alpha format in order to use the catenation operator. EDIT() will do that, if you don't mind the leading zeros (otherwise use FTOA and LJUST)

  
  DEFINE FILE CAR
    PACKED/P7=LENGTH;
  END
  TABLE FILE CAR
    BY PACKED
    ON TABLE HOLD
  END
-RUN
-SET &NN=(7+2)*&LINES-2;
  DEFINE FILE HOLD
    I/I2=I+1;
    STRING/A&NN|V = IF (I EQ 1) THEN EDIT(PACKED) ELSE STRING || (', '| EDIT(PACKED));
  END
  TABLE FILE HOLD
    WRITE LST.STRING/A&NN
    ON TABLE SAVE
	ON TABLE SET HOLDLIST PRINTONLY
  END  
-RUN
-READ SAVE &STRING.A&NN.EVAL
-? &STRING

results in

&STRING       = 0000163, 0000165, 0000170, 0000176, 0000177, 0000182, 0000187

I'm sorry...missed the part where you wanted it separated by comma's. Yes you could do mine with an IF. ...but guess you are on to what you really wanted.

You could also just use a loop with a -READ.

TABLE FILE CAR
SUM
MAX.COUNTRY AS COUNTRY
BY COUNTRY NOPRINT
ON TABLE SAVE AS HCOUNTRY FORMAT ALPHA
END
-RUN
-SET &COUNTER=&LINES - 1;
-READ HCOUNTRY &STRING.A10 NOCLOSE
-REPEAT READLIST &COUNTER.EVAL TIMES
-READ HCOUNTRY &VALUE.A10
-SET &STRING='&STRING.EVAL'||','||'&VALUE.EVAL';
-READLIST
-TYPE &STRING

You could also use the ACROSS verb to cut out concatenation after a repeated loop to read the &vars. If you use FORMAT COM then you only need to replace the double quotes by singles where necessary as the format will only add double quotes around alphanumeric values and not numeric. Less steps ....

You need to ensure that your resultant variable doesn't exceed limits for the IN statement.

APP PREPENDPATH IBISAMP
TABLE FILE CAR
SUM COUNTRY
ACROSS COUNTRY NOPRINT
ON TABLE SAVE AS MYHOLD FORMAT COM
END
-RUN
-* Make sure that this read is long enough
-READ MYHOLD &COUNTRY.A100
-RUN
-SET &COUNTRY = STRREP(&COUNTRY.LENGTH, '&COUNTRY.EVAL', 1, '"', 1, '''', &COUNTRY.LENGTH, 'A&COUNTRY.LENGTH');
TABLE FILE CAR
SUM RCOST DCOST
 BY COUNTRY
 BY CAR
 BY MODEL
WHERE COUNTRY IN (&COUNTRY)
END
-RUN

T

Another possibility is to ensure that all values have quotes around them and a trailing comma. Then find the number of countries that answer screening conditions. Finally, as Tony suggests, use ACROSS for putting all values on one line. A finishing touch to remove the last comma.

  
-* File create_variable.fex
-* To prevent non-printing fields from being saved in the HOLD file 
SET HOLDLIST=PRINTONLY
-*
-* Add quotes and comma
DEFINE FILE CAR
QCOUNTRY/A13='''' | COUNTRY || ''',';
END
-*
-* Count the number of selected countries
TABLE FILE CAR
BY QCOUNTRY
WHERE SALES GT 15000;
ON TABLE HOLD
END
-RUN
-*
-* Calculate the full length of the saved line
-SET &QLEN=13 * &LINES;
-*
-* Save the line
TABLE FILE HOLD
SUM QCOUNTRY
ACROSS QCOUNTRY
ON TABLE SAVE
END
-RUN
-*
-* Read the saved values
-READ SAVE &COUNTRY.A&QLEN.EVAL
-*
-* Do away with the last comma
-SET &COUNTRY = &COUNTRY || ',';
-SET &COUNTRY = TRIM('T', &COUNTRY, &QLEN, ',', 1, A&QLEN.EVAL);
-*
-* Run the program
TABLE FILE CAR
SUM SALES
 BY COUNTRY
 BY CAR
 BY MODEL
WHERE COUNTRY IN (&COUNTRY)
END

The wonders of WF!

Bethany,

You have been given a lot of solutions, but what are you trying to accomplish? Why do you want the values in an amphere variable separated by commas?

Here are two more solutions to consider:

This one use Define to build the quoted string and is suitable for feeding an SQL passthru

DEFINE FILE CAR
STRING_WK/A512V = IF LAST STRING EQ ' ' THEN '''' || COUNTRY || ''''
ELSE LAST STRING || ' , ''' || COUNTRY || '''';
END
TABLE FILE CAR
SUM
-** THIS COMPUTE IS NECCESARY TO REMOVE LENGTH IN VARIABLE FORMAT FIELD
COMPUTE STRING/A512 = STRING_WK;
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE SAVE AS STROUT
END
-RUN

TABLE FILE CAR
PRINT
COUNTRY MODEL DCOST RCOST
WHERE COUNTRY IN (
-INCLUDE STROUT.FTM
)
END
-RUN

This one will work with a table request

TABLE FILE CAR
BY COUNTRY
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE SAVE AS STROUT
END
-RUN

TABLE FILE CAR
PRINT
COUNTRY MODEL DCOST RCOST
WHERE COUNTRY IN FILE STROUT
END
-RUN

Thanks for the many suggestions. I'm just getting back to working on this report and I'm still having problems. The big picture is for an FML report that needs to have particular formatting. Due to the way WHEN EXISTS works in FML, I need to determine whether a FOR value will exist in the final report and if not then skip the code for that particular FOR value.

This is the basic code I'm working with now.
This is to set the countries that I need in my output if they do exist;

-SET &COUNTRY1 = 'ENGLAND';
-SET &COUNTRY2 = 'ITALY';
-SET &COUNTRY3 = 'W GERMANY';
 

This would be the part where I get list of countries that meet a certain criteria and create the list of the ones that actually do exist for my final output.

TABLE FILE CAR
SUM
COUNTRY
    BY COUNTRY NOPRINT
	WHERE COUNTRY IN ('ENGLAND', 'ITALY');
   ON TABLE HOLD
END
-SET &N=&LINES;
-SET &NN=12*&N;
  DEFINE FILE HOLD
    I/I2=I+1;
    STRINGV/A&NN|V = IF (I EQ 1) THEN  '''' | COUNTRY | '''' ELSE STRING || (', '| '''' | COUNTRY || '''');
    STRING /A&NN   = STRINGV;
  END
  TABLE FILE HOLD
    WRITE LST.STRING
    ON TABLE SAVE
  END
-RUN
-READ SAVE &STRING.A&NN.EVAL

Here in the final output. I want to see if &COUNTRY1 (ENGLAND) is in the list of countries and if it's not then skip the code that would include it on the report. Then check to see if &COUNTRY2 is in the list etc...

  
TABLE FILE CAR
SUM
DEALER_COST
RETAIL_COST
FOR COUNTRY
-IF &COUNTRY1 NOT IN (&STRING) GOTO SKIP1;
ENGLAND OVER
-SKIP1
-IF &COUNTRY2 NOT IN (&STRING) GOTO SKIP2;
ITALY OVER
-SKIP2
-IF &COUNTRY3 NOT IN (&STRING) GOTO SKIP3;
W GERMANY OVER
-SKIP3
BAR
END  

I'm getting errors that "IN" must appear in a valid clause and if I don't get that message, it's still not correctly determining if &COUNTRY1,2,3 is actually in the list.

Thanks again for all of your suggestions.
Bethany

Didn't quite understand your big picture for what you are trying to accomplish, but aside from that - NOT IN is not valid syntax for what you are doing. NOT IN file is used to validate a value against a file and is used in a WHERE or IF statement within a TABLE command. It cannot be used to check a string or in Dialogue Manager.

You might try
-IF &STRING OMITS 'ENGLAND' or -IF &STRING NOT-LIKE %ENGLAND% which should both work in Dialogue Manager statements.

Bethany,

If you have the list of values in a file then just read through that file using a repeat loop -

APP PREPENDPATH IBISAMP
TABLE FILE CAR
BY COUNTRY
WHERE COUNTRY CONTAINS 'E'
ON TABLE SAVE AS CTRY_S1
END
-RUN
TABLE FILE CAR
SUM DEALER_COST
    RETAIL_COST
FOR COUNTRY
-READ CTRY_S1, &Country
-REPEAT :Loop WHILE &IORETURN EQ 0;
'&Country.EVAL' OVER
-READ CTRY_S1, &Country
-:Loop
""
ON TABLE SET HTMLCSS ON
ON TABLE SET PAGE NOLEAD
END

T