Joining complex tables

Question

I'm trying to combine a couple of tables together and am having some problems. Basically my data sheets look like the following

AgeSexPatient A13MPatient B15F

DiagnosisPatient A

DiabetesPatient AHTNPatient BAsthmaPatient BHTNPatient BSepsis

I'm trying to make a table to combine the diagnoses with the patient codes (to look like the table below), but each patient diagnosis is represented individually in my current excel format rather than combining each patient with all their diagnoses. Is there an easy way to combine these two tables? Thanks!

AgeSexDiabetesHTNAsthmaSepsisPatient A13M1100Patient B15F0111

Jeff_Perkinson · Accepted Answer

This is pretty easy to do with a little manipulation using Tables->Split on the Diagnosis table first. You just need to add a third column of 1s that you'll split.

Then it's a matter of search and replace the missing values (.) with 0s and use Tables->Join by Patient.

txnelson · Accepted Answer

Jeff's answer is a great answer.  Here is my version, which uses the JSL created using similar steps that Jeff used, to create the new table

names default to here(1);

dtData = New Table( "The Data",
	Add Rows( 5 ),
	New Column( "Patient ID",
		Character,
		"Nominal",
		Set Values(
			{"Patient A", "Patient A", "Patient B", "Patient B", "Patient B"}
		)
	),
	New Column( "Diagnosis",
		Character( 16 ),
		"Nominal",
		Set Values( {"Diabetes", "HTN", "Asthma", "HTN", "Sepsis"} )
	)
);
dtDemo = New Table( "The Demographics",
	Add Rows( 2 ),
	New Column( "Patient ID",
		Character,
		"Nominal",
		Set Values( {"Patient A", "Patient B"} )
	),
	New Column( "Age", Character( 16 ), "Nominal", Set Values( {"13", "15"} ) ),
	New Column( "Sex", Character( 16 ), "Nominal", Set Values( {"M", "F"} ) )
);

// Create a list with just the Patient IDs
summarize( dtData, theIDs = by( :Patient ID));
// Move it into a data table
dtIDs = New Table("theIDs", invisible, new column("Patient ID", character, values(theIDs)));

// Create a list with just the Diagnoses
summarize( dtData, theDiags = by( :Diagnosis));
// Move it into a data table
dtDiags = New Table("theDiags", invisible, new column("Diagnosis", character, values(theDiags)));

// Create a table with all possible combinations of patients and diagnoses
dtCart = dtIDs << Join( With( dtDiags ), Cartesian Join );

// Cleanup
close( dtIDs, nosave );
close( dtDiags, nosave);

// Add a null diagnosis column
dtCart << New Column("foundDiag", set each value(0));

dtData << New Column("foundDiag", set each value(1));

// Put the tables together
dtCart << Update(
	With( dtData),
	Match Columns( :Patient ID = :Patient ID, :Diagnosis = :Diagnosis )
);

// Split the data into the final form
dtSplit = dtCart << Split(
	Split By( :Diagnosis ),
	Split( :foundDiag ),
	Group( :Patient ID ),
	Sort by Column Property
);

// Make a copy of the demographics data table
dtFinal = dtDemo << subset( selected columns(0), selected rows(0));

// put the demographics file together with the split data table
dtFinal << Update(
	With( dtSplit ),
	Match Columns( :Patient ID = :Patient ID )
);

// Cleanup
close( dtSplit, nosave );
close( dtCart, nosave );