Subscribe Bookmark RSS Feed

Creating a column

jstewart

Community Trekker

Joined:

Jan 10, 2012

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 MortalityDeath
1
1
1*1
2

2

2

3
1
3
1
3*1
4

4

5
1
5*1
1 ACCEPTED SOLUTION

Accepted Solutions
Solution

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

5 REPLIES
jsurette

Community Trekker

Joined:

Jun 23, 2011

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

Super User

Joined:

Jun 23, 2011

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;

);

ms

Super User

Joined:

Jun 23, 2011

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

          )

);


Solution

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

jstewart

Community Trekker

Joined:

Jan 10, 2012

Thanks for all of your help!