I am working on a dataset containing numbers of about 200 patients showing multiple entries of different lab procedures, outcome of these procedures and teh actual date at which the procedure was executed.
I would like to add two indicator variables:
1) one indicator indicating for each patient and each lab. procedure the last date in the file at which the specific procedure was executed
2) one indicator indicating for each patient and each laboratory procedure the last date within the last 12 month at which that specific procedure was executed.
The aim of these indicators is to answer questions like "how many patient have undergone a specific procedure in the last 12 months" or "how many patients underwent a specific procedure over time". In both questions double counting needs to be avoided.
To visualise this problem in an example:
patient procedure date of procedure
1 a date1
1 b date1
1 c date2
1 a date3
1 c date3
1 a date4
2 d date2
2 d date5
2 a date3
in this example it is obvious that the first indicator I am looking for should point for patient 1 procedure a at date4, for patient 1 procedure b at date1 and for patient 1 procedure c at date 3. Moreover assume date 4 of patient 1, procedure a, is less then 12 month ago, the second indicator I am looking for should indicate this.
For patient 2 with procedure d, date5 should be pointed at and for patient 2 with procedure a, date3 should be pointed at with the indicator etc.
I tried by sorting the dataset first in the order of patient, procedure and date of procedure and then use the function firstsortedvalue on procedure and date of procedure. I got lots of error messages most often that the function is misspeld or misused in the dataset.
I tried by sorting the dataset first in the order of patient, procedure and date of procedure and then use the date(max(date of procedure)) function to create that indicator, but again misspelling was reported by the system.
in order to find the indicator indicating dates within 12 month ago, I tried to use an if function like date(max(if( [date of procedure] >= today()-365,1))) but again I got continuously messages of misspelling in the script.
My question is how to create in the most easiest way an indicator for by groups in a dataset, indicating first or last occurences in that by group?
Johan Velema
GP practice Mozaiek
Den Haag
The Netherlands
