I am sure this can be done but cannot find the solution. I have 2 hold files. One is a list of user_ids and one is a list of all calendar dates for specific month. I need to merge the 2 files into a single file for subsequent reporting. For example:

user_id
1234
5846
8412

Calendar days in text format
2014_10_01
2014_10_02
2014_10_03
2014_10_04
2014_10_05

Ultimately I need to concatenate the fields into the following:
2014_10_02_1234
2014_10_03_1234
2014_10_04_1234
2014_10_05_1234
2014_10_01_5846
2014_10_02_5846
2014_10_03_5846
2014_10_04_5846
2014_10_05_5846
2014_10_01_8412
2014_10_02_8412
2014_10_03_8412
2014_10_04_8412
2014_10_05_8412

Any ideas on how to do this? I tried match but that did not produce what I needed.

Thank you,
Geri Gellman

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

You can MacGyver it.

  
SET HOLDLIST = PRINTONLY

DEFINE FILE CAR
BLANK/A1 = ' ';
END

TABLE FILE CAR
SUM
BLANK NOPRINT
BY BLANK
PRINT
CAR
BY BLANK
ON TABLE HOLD AS HOLD1 FORMAT FOCUS INDEX BLANK
END

JOIN BLANC WITH RELDATE IN MOVIES TO UNIQUE BLANK IN HOLD1 AS J1

DEFINE FILE MOVIES
BLANC/A1 = ' ';
END

TABLE FILE MOVIES
PRINT
CAR
RELDATE/YYMD
COMPUTE DATE_TXT/A9 = EDIT(RELDATE, '99_99_99_' );
COMPUTE NEWFIELD/A50 = DATE_TXT | CAR;
BY CAR
ON TABLE PCHOLD FORMAT AHTML
END

quote:

SET HOLDLIST = PRINTONLY

DEFINE FILE CAR
BLANK/A1 = ' ';
END

TABLE FILE CAR
SUM
BLANK NOPRINT
BY BLANK
PRINT
CAR
BY BLANK
ON TABLE HOLD AS HOLD1 FORMAT FOCUS INDEX BLANK
END

JOIN BLANC WITH RELDATE IN MOVIES TO UNIQUE BLANK IN HOLD1 AS J1

DEFINE FILE MOVIES
BLANC/A1 = ' ';
END

TABLE FILE MOVIES
PRINT
CAR
RELDATE/YYMD
COMPUTE DATE_TXT/A9 = EDIT(RELDATE, '99_99_99_' );
COMPUTE NEWFIELD/A50 = DATE_TXT | CAR;
BY CAR
ON TABLE PCHOLD FORMAT AHTML
END

Above is exactly what I needed! Thanks!

You could also use a condition based join.

Please see this

Just don't specify a where clause.

...it took me some time to find a simple solution for this.

But I think I did.

SET ASNAMES = ON
TABLE FILE CAR
	BY	CAR
ON TABLE HOLD AS HLD_CAR
END

TABLE FILE CAR
	BY	COUNTRY
ON TABLE HOLD AS HLD_COUNTRY
END

-* SQL join
SQL
SELECT
	HLD_CAR.CAR, HLD_COUNTRY.COUNTRY
FROM HLD_CAR
INNER JOIN HLD_COUNTRY;
TABLE HOLD AS HLD_CARTESIAN
END
-RUN

TABLE FILE HLD_CARTESIAN
	PRINT *
END

Yes, this works.
You can even add join-conditions ( in sql ).
Or even some other SQL stuff.

SQL
insert into HLD_CARTESIAN ( CAR , COUNTRY ) VALUES ( 'DODGE' , 'USA' );
END

tags for future reference:
cartesian
cartesian product
sql join
conditional join
sql insert

[ should we start using #hashtags?? ]