Joining complex tables

[#1]

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

	Age	Sex
Patient A	13	M
Patient B	15	F

	Diagnosis
Patient A	Diabetes
Patient A	HTN
Patient B	Asthma
Patient B	HTN
Patient B	Sepsis

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!

	Age	Sex	Diabetes	HTN	Asthma	Sepsis
Patient A	13	M	1	1	0	0
Patient B	15	F	0	1	1	1

[#2 · Accepted solution]

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.

 

 

 

[#3 · Accepted solution]

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 );