Hi,

I'm trying to do a union on two different hold files that have no related columns. Is there a simple way to do this? Basically, I just want to merge the CAR and EMPLOYEE table.

TABLE FILE CAR
PRINT *
ON TABLE HOLD AS CAR_HOLD
END
-RUN

TABLE FILE EMPLOYEE
PRINT *
ON TABLE HOLD AS EMPLOYEE_HOLD
END
-RUN

This message has been edited. Last edited by: FP Mod Chuck,

Are you looking for a Carthesian product?
You can do that by adding a virtual dummy column to both tables and joining on that.

Basically, you can't merge two tables that have nothing in common.
You could if you have the same number of column (field) and format in both files. To do so, you could use the MORE feature.

Merging CAR and EMPLOYEE files where they don't have the same number of fields and where "position columns matching" (col1 : CAR.COUNTRY vs col1 : EMP.EMP_ID) doesn't have the same format : A10 vs A9, can't be done.
Focus need to have the same column's name, number and format to be able to merge.

You could use MATCH OLD-OR-NEW to have both files "added together", but the column's from EMPLOYEE file will only be added beside the one from CAR file.
And even that, you need a common column (field) to perform the MATCH even if the value don't match (not same value).
To not have two different set of data as below sample, you need that the Matching key have the same value (DUMMY = 1 in both files) but you won't have the same data set : 37 rows using below sample vs 19 rows when DUMMY = 1 in both files.

SET ASNAMES  = ON
SET HOLDLIST = PRINTONLY

TABLE FILE CAR
PRINT *
BY TOTAL COMPUTE DUMMY/A1 = '1';
ON TABLE HOLD AS XCAR
END
-RUN

TABLE FILE EMPLOYEE
PRINT *
BY TOTAL COMPUTE DUMMY/A1 = '2';
ON TABLE HOLD AS XEMP
END
-RUN

MATCH FILE XCAR
PRINT
    COUNTRY
    CAR
    MODEL
    BODYTYPE
    SEATS
    DEALER_COST
    RETAIL_COST
    SALES
    LENGTH
    WIDTH
    HEIGHT
    WEIGHT
    WHEELBASE
    FUEL_CAP
    BHP
    RPM
    MPG
    ACCEL
BY DUMMY
RUN
FILE XEMP
PRINT
    EMP_ID
    LAST_NAME
    FIRST_NAME
    HIRE_DATE
    DEPARTMENT
    CURR_SAL
    CURR_JOBCODE
    ED_HRS
    BANK_NAME
    BANK_CODE
    BANK_ACCT 
    EFFECT_DATE
    DAT_INC
    PCT_INC
    SALARY
    JOBCODE
BY DUMMY
ON TABLE HOLD AS MRG OLD-OR-NEW
END
-RUN

TABLE FILE MRG
PRINT *
END
-RUN

Or as Wep5622 mentioned, if you want a Cartesian product, use JOIN with a DUMMY field. With this you should have 18 * 19 = 342 records

SET ASNAMES  = ON
SET HOLDLIST = PRINTONLY

TABLE FILE CAR
PRINT *
BY TOTAL COMPUTE DUMMY/A1 = '1';
ON TABLE HOLD AS XCAR FORMAT FOCUS
END
-RUN

TABLE FILE EMPLOYEE
PRINT *
BY TOTAL COMPUTE DUMMY/A1 = '1';
ON TABLE HOLD AS XEMP FORMAT FOCUS INDEX DUMMY
END
-RUN

JOIN 
              DUMMY IN XCAR
  TO MULTIPLE DUMMY IN XEMP TAG J1 AS J1
END

TABLE FILE XCAR
PRINT *
END
-RUN

Here a sample using MORE where column's name, number and format match in "MOREd" files

SET ASNAMES  = ON
SET HOLDLIST = PRINTONLY

TABLE FILE CAR
PRINT COMPUTE COL1/A30 = FPRINT(COUNTRY, 'A10', 'A30');
      COMPUTE COL2/A20 = FPRINT(RETAIL_COST, 'D7', 'A20');
ON TABLE HOLD AS XCAR FORMAT FOCUS
END
-RUN

TABLE FILE EMPLOYEE
PRINT COMPUTE COL1/A30 = FPRINT(EMP_ID, 'A9', 'A30');
      COMPUTE COL2/A20 = FPRINT(HIRE_DATE, 'I6YMD', 'A20');
ON TABLE HOLD AS XEMP FORMAT FOCUS
END
-RUN

TABLE FILE XCAR
PRINT COL1
      COL2
MORE
FILE XEMP
END
-RUN

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

Wep, Cartesian Product was actually exactly what I was looking for. I looked up how to do this and came across http://forums.informationbuild...1057331/m/7161077331 which is what I want.

Sorry for the duplicate post.

quote:

Originally posted by MartinY:

JOIN
DUMMY IN XCAR
TO MULTIPLE DUMMY IN XEMP TAG J1 AS J1
END

MartinY,
Join to Multiple Dummy; you have me in tears.

Thanks for the laugh.

Have a great day