Say, I have 3 area codes coming from Parameter page....

I need to replicate all the rows in a Hold file for every area code....


-SET &INAREA_CODE = '''AA'' OR ''BB'' OR ''CC''';

TABLE FILE CAR
PRINT
DST.COUNTRY
ON TABLE HOLD AS HLD1
END



For every entry in &INAREA_CODE All the rows in HLD1 has to be replicated.

I need the output like...



I hope I have made it clear.

How to go about this ?

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

There are many ways to do this, here is one:

-SET &ECHO=ALL;

SET ASNAMES    = ON
SET HOLDLIST   = PRINTONLY
SET HOLDFORMAT = ALPHA
SET BYDISPLAY  = ON
-RUN

FILEDEF MASTER DISK areacode.mas
-RUN

-WRITE MASTER FILENAME=AREACODE, SUFFIX=FIX
-WRITE MASTER SEGNAME=AREACODE
-WRITE MASTER FIELDNAME=AREACODE, USAGE=A02, ACTUAL=A02, $
-RUN

FILEDEF AREACODE DISK areacode.txt
-RUN

-WRITE AREACODE AA
-WRITE AREACODE BB
-WRITE AREACODE CC

TABLE FILE AREACODE
SUM
COMPUTE DUMMY1/A1 = '';
BY AREACODE
ON TABLE HOLD AS HAREACODE FORMAT FOCUS INDEX DUMMY1
END
-RUN

?FF HAREACODE
-RUN

JOIN CLEAR *
JOIN DUMMY1 WITH COUNTRY IN CAR TO ALL DUMMY1 IN HAREACODE AS J1
-RUN

DEFINE FILE CAR
DUMMY1/A1 WITH COUNTRY = ' ';
END
-RUN

TABLE FILE CAR
SUM
AREACODE NOPRINT
BY COUNTRY
BY AREACODE
-*ON TABLE HOLD AS HCAR
END
-RUN

This is called the "MacGyver" technique - here is a detailed description of this: MacGyver Techniques.

Another option is to use the FOR command. It forces the existence of the specified values, as long as one of them exists.

DEFINE FILE CAR
AREACODE/A3 = ' ';
END

-RUN

TABLE FILE CAR
BY COUNTRY
FOR AREACODE
' ' OVER
'AAA' OVER
'BBB' OVER
'CCC'
ON TABLE HOLD AS HCAR
END

-RUN

DEFINE FILE HCAR
 AREACODE/A3=E02 ;
END

TABLE FILE HCAR
BY COUNTRY
BY AREACODE
WHERE AREACODE NE ' '
END

-RUN