I have to sets of data that I need and do not know how to use. One table (excel spreadsheet) includes a list of cases and its associated visit information. The second table (second excel spreadsheet) is a reference table that includes list of diagnoses of interest.
In the cases table with all the "raw data", I have 5 columns referencing the case's diagnoses (Diag1, Diag2, Diag3, Diag4, Diag5). The reference table only has 3 columns (Diag_Code_No, Diag_Group, Diag_Description).
What I need:
I need to create a table or expression or variable that I can use that uses the diagnosis reference table and assigns one of the Diag_Group to each case based on those 5 columns from the cases table.
If more than one Diag-Group applies, it will only assign the one with the the highest ranked Diagnosis. For example, if one case has a Diagnosis of choice in Diag1 and Diag 3, the Diag-Group assigned to it will be for Diag1 as this is considered the primary diagnosis.
How can I accomplish this or what tutorial can I reference to get started?
Attached is some sample data and what I need it to look like. I need the final table to have a column that assigns the Grouped ICD9 code to that line item (visit) based on the reference spreadsheet.
I believe it is a form of concatanate/join, but am unsure how to do it, how to script it (syntaxt), etc.
I see what you mean and why it is hard. I have made changes to the reference table so that it would have the Diagnosis1 column match by name. The formats are the same too, so it should not be an issue.
I have attached the updated documents.
What I thought should work was the following, but I know it is wrong and need help finishing up. The idea is to make this the load script that would create the master combined table that will later be loaded as a separate file.
LOAD * // Note here that DiagCode1 is a field in this data
[C:\Users\. . . . . \Routes_by_Dx.xlsx]
(ooxml, embedded labels, table is Sheet1);
Left Join (RawData)
LOAD DiagCode1, // Note that this is the column the two tables will have in common to join on
[ICD-9 Code Description]
[C:\Users\. . . . . . . \Selected ICD 9 Codes.xlsx]
(ooxml, embedded labels, table is Final);
STORE ReferenceTbl into [C:\Users\ . . . . . \CombinedTable.csv](txt);