4298_en-US

liliconcom

Creating a column<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />

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

Creating a column

Thanks for all of your help!

Re: Creating a column

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

Creating a column

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

          )

);

Creating a column

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;

);

Creating a column

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