Years since<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />I have a large databse of patients hospitalized for a certain condition. I have been trying to make a new column to indicate the "years prior to death". See below:
| YEAR | patient | Mortality_in365days | Death year | Years before death |
|---|
| 1990 | 221 |
| 1995 | 5 |
| 1991 | 221 |
| 1995 | 4 |
| 1991 | 221 |
| 1995 | 4 |
| 1993 | 221 |
| 1995 | 2 |
| 1995 | 221 | * | 1995 | 0 |
| 2000 | 789 |
| 2002 | 2 |
| 2001 | 789 |
| 2002 | 1 |
| 2002 | 789 | * | 2002 | 0 |
| 2001 | 965 |
| 2005 | 4 |
| 2005 | 965 | * | 2005 | 0 |
A star in mortality_in365days column means they died within the last 365 days. I know the years before death will not be an exact calculation since depending on how long ago they died (30 days versus 365 days) it may mean they died the year prior. I had set up JMP to make the death year column, and then make a simple subtraction formula to calculate years before death, but then I run into problems. I had done it manually using the update feature since I am not very good at scripting yet. If you can help me either with manual instructions or scripting please let me know. Also, if you have any reccommendations for scripting books for JMP I would appreciate it.
Years sinceHere's a formula to compute Death Year that you can paste it into the formula editor:
Local( {pat, rows},
pat = :patient;
rows = Current Data Table() << Get Rows Where( :Mortality_in365days == "*" & :patient == pat );
If( N Rows( rows ) > 0,
:YEAR[rows][1],
.
);
)
It returns the year for the first row where the mortality has "*" and the patient is the same ID as the current patient.
