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 GellmanThis message has been edited. Last edited by: Geri,
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
June 12, 2015, 01:24 PM
Geri
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
...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