nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Creating a column

jstewart

I am a JMP novice and need some help. I have a large database that tracks hospitalized patients over a 20 year period. If a patient has a "*" in column "365 Mortality", I would like to put a 1 in column "Death" for every time that patient is listed. See below.

Patient	365 Mortality	Death
1		1
1	*	1
2
2
2
3		1
3		1
3	*	1
4
4
5		1
5	*	1

Accepted answers

mpb

And another approach:

dtmort = Data Table( "Mortality" );

dtby = dtmort << Summary( Group( :Name( "365 Mortality" ), :Patient ), Link to original data table( 0 ) );

dtby << select where( :name( "365 Mortality" ) != "*" );

dtby << delete rows;

dtby << New Column( "Death1", Numeric, Continuous, Format( "Best", 9 ), set each value( 1 ) );

dtby << delete columns( "365 Mortality" );

dtmort << Update( With( Data Table( dtby ) ), Match Columns( :Patient = :Patient ), Add Columns from Update table( :Death1 ) );

close( dtby, nosave);

All comments

jsurette

This can be done with an if statement, either through the column info pane or JSL. Here is quick example of the JSL

dt = current data table();

dt << New Column( "Death", Formula( If(:Name("365 Mortality") == "*", 1) ));

pmroz

I think it's a little trickier than that. jstewart wants a 1 in the Death column if any patient row has a *. Here's one way to do it:

dt = data table("Mortality");

dt << New Column( "Death1", Numeric, Continuous, Format( "Best", 9 ));

dt1 = (dtab = dt << Tabulate(

Show Control Panel( 0 ),

Add Table( Row Table( Grouping Columns( :Patient, :Name( "365 Mortality" ) ) ) )

)) << Make Into Data Table;

dtab << close window;

for (i = 1, i <= nrows(dt1), i++,

patient_no = column(dt1, "Patient")[i];

pat_rows = dt << get rows where(:Patient == eval(patient_no));

column(dt, "Death1")[pat_rows] = 1;

);

Here's another way to do it. The approach can be applied to a column formula (run this script or paste the code inside Formula( ) into a new columns formula property):

dt << New Column( "death",

formula( If( Associative Array( As List( :Patient << get values ), :Name( "365 Mortality" ) << get values )[:Patient[]] == "*", 1 ) )

);

Or alternatively, If there is no need for a dynamic formula or if the database is very large (complex formulas may slow things down), it can be done by a jsl script:

dt << Current Data Table();

col = (dt << New Column( "death" ));

For Each Row(

If( Associative Array( As List( :Patient << get values ), Column( "365 Mortality" ) << get values )[:Patient[]] == "*",

col[] = 1

)

);

mpb