Medical Data cleaning in JMP from multiple excel sheets imported into JMP?

[#1]

Hello,

please kindly help me to find a more comfortable solution in JMP - rather what I do know, use excel incl VBA and import this into jmp:

my medical database research is usually structured like, eg (master sheet):

Unique identifier	Age	Weight	Comorbidity 1 = Heart disease	Comorbidity 2 = pulmonary disease
AA	32	65	yes	no
AX	34	67	yes	yes

an excel tab usually has ICD9/10 codes, eg:

Identifier	ICD Code
AA	Q26.4
AA	776.4
AA	776.41
AX	775.39
AX	775.38

I created excel sheets for Comorbidity 1 = Heart Disease and Comorbidity 2 = Pulmonary Disease as per ICD diagnosis codes.

I usually use vlookup, countif, match or sometimes simple manual filter function to compare my ICD coding sheet to the excel sheet above; in this particular case heart disease (775.3X and 776.4X) or pulmonary disease (Q26.4) to identify my Identifiers where this variable is present. I copy and paste those identifiers into a new excel sheet, remove the duplicates and import those into JMP, where I append those via Tables "Update" into my MasterSheet , which results into as presented in my master sheet, as above.

I am looking to do this cumbersome process instead in excel and importing into JMP, directly in JMP: identifying different predefined diagnosis group codes (sometimes with spaceholder X to identify multiple subcodes in one category) for their unique identifier (delete multiple listing for the same diagnosis group) and add this an extra column to my master jmp sheet.

Thanks a lot for any advice !! - Sorry I am not an advanced JMP user, and haven't used JSL before.

[#2]

Data manipulation is one of JMP's real strengths.  In cases such as what you are describing, the usual issue is not getting JMP to manipulate the data, but rather identifying the rules in your data on what needs to be done.  i.e. , your use of X to indicate multiple subcategories.  Do you want to collapse to just an overall category, or do you want to expand and match to all subcategories?......or both?   Once that rule is determined, getting JMP to do what is needed, is usually pretty easy.

My suggestion, to get started, is to create some Anonymized data that can be shared, and to describe in detail, what the final output of the data needs to look like.

That should give a good environment to start the process.

Also, you need to take the time to read the Scripting Guide from the JMP Documentation Library, available under the Help pull down menu.  If you have used Excel with VBA extensions, you should be able to move into JSL with ease.