From 2 files to 1 cases per id

* 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.