Merge and aggregate datasets: Difference between revisions

pStruct := {{"patient_id", "n", 8, 0}, {"lastname", "c", 10, 0 }}

dbCreate( "patient", pStruct,, .T., "patient" )

INDEX ON patient_id TO pat_id

dbCreate( "visit", vStruct,, .T., "visit" )

INSERT INTO visit (patient_id, visit_date, score) VALUES (2002, ctod("2020-09-10"), 6.8)

INSERT INTO visit (patient_id, visit_date, score) VALUES (4004, ctod("2020-09-24"), 8.4)

INSERT INTO visit (patient_id, visit_date) VALUES (2002, ctod("2020-10-08"))

INSERT INTO visit (patient_id, score) VALUES (1001, 6.6)

INSERT INTO visit (patient_id, visit_date) VALUES (3003, ctod("2020-11-12"))

INSERT INTO visit (patient_id, visit_date, score) VALUES (1001, ctod("2020-11-19"), 5.3)

INDEX ON patient_id TO visit_id

DO WHILE ! Eof()

xCurId := patient_id && grouping variable

DO WHILE ! Eof() .AND. xCurId == patient_id

ENDDO

INSERT INTO report (patient_id, n, sum_score, avg_score, max_date) ;

ENDDO

 

 

<pre>NUM PATIENT_ID LASTNAME N SUM_SCORE AVG_SCORE MAX_DATE

3 3003 Kemeny 1 0.0 0.0 2020-11-12

5 5005 Kurtz 0 0.0 0.0 - - </pre>