* Going from 2 files to 1 case per id. * Note that one does not have to know in advance the maximum number of cases per id in the second file. * This is determined by the syntax and the code is self-adjusting in accordance. * Raynald Levesque rlevesque@videotron.ca. * ------------ First define some data. DATA LIST LIST /id(F8) vara(F8) varb(F8). BEGIN DATA. 1 10 12 2 15 17 3 10 20 4 20 30 END DATA. LIST. SAVE OUTFILE='c:\\temp\\id data.sav'. DATA LIST LIST /id(F8) date1(ADATE) test(F8). BEGIN DATA. 1 10/1/99 1 1 11/2/99 2 1 12/12/99 2 2 1/1/99 1 2 2/1/99 3 3 3/1/99 1 3 4/1/99 2 3 5/1/99 4 3 6/1/99 5 END DATA. LIST. SAVE OUTFILE='c:\\temp\\tests data.sav'. * ------------------ Now count the maximum number of tests in the file for a single id. * ------------------ then create a macro which will give us that number. * ------------------ finally, include that macro in this syntax. RANK test BY id /N INTO n. COMPUTE dummy=1. AGGREGATE /OUTFILE=* /BREAK=dummy /n = MAX(n). WRITE OUTFILE 'c:\\temp\\temp.sps' /"DEFINE !n()" n "!ENDDEFINE.". EXECUTE. INCLUDE 'c:\\temp\\temp.sps'. * ------------------ Now start the job. * Number the tests. GET FILE='c:\\temp\\tests data.sav'. DO IF $CASENUM=1. COMPUTE case_nb=1. ELSE IF id=LAG(id). COMPUTE case_nb=LAG(case_nb)+1. ELSE. COMPUTE case_nb=1. END IF. * Distribute the cases to the appropriate columns. VECTOR tests(!n F8.0) dates(!n ADATE8). COMPUTE dates(case_nb)=date1. COMPUTE tests(case_nb)=test. * Define macro which will reduce the cases to a single case per id. DEFINE !agg (numb=!TOKENS(1)) AGGREGATE /OUTFILE=* /BREAK=id /test1 TO !CONCAT('test',!n) =MAX(tests1 TO !CONCAT('tests',!n)) /date1 TO !CONCAT('date',!n) =MAX(dates1 TO !CONCAT('dates',!n)) . !ENDDEFINE. *Call macro to obtain one line per id. !agg numb=!n. * Now match the 2 files. MATCH FILES /FILE=* /FILE='c:\\temp\\id data.sav' /BY id. EXECUTE.