I have a file of lab data in which every patient has multiple values from measurements taken on different days. It looks like this:
BP1 BP2 BP3 BP4 BP5 date1 date 2 date 3 date 4 date 5 (BP1 was measured on date1 etc)
I'm interested in the date on which the lowest BP value was measured. I have been able to use a command to find what the lowest BP value was and also what date variable it can be found in, so for example: I know that for a specific patient the lowest BP was 75 and that this value is in the BP3 variable for that patient. But I want to tell SPSS that if BP3 contains the lowest value, that it should copy the date in "date3" to a new variable so I have a list with the dates on which the values were the lowest.
Hello Thom,
SPSS offers a simple operator, MIN, which may be used to capture the minimum value from a set of variables; e.g., compute lowest_value = MIN(var1, var2, var3, var4, var5) .
Good luck with your work.
Dear Thom,
in such a case 'do repeat' comes in handy. I'll make an artificial small dataset to illustrate:
data list notable
/BP1 1-3, BP2 5-7, BP3 9-11,
Date1 13-22(edate), Date2 24-33(edate), Date3 35-44(edate).
begin data.
60 80 90 17.06.2021 01.07.2020 15.09.2019
100 90 50 03.08.2020 01.09.2018 17.10.2017
110 40 100 02.04.2016 03.02.2015 07.12.2015
150 50 50 03.07.2014 23.12.2022 04.10.2014
end data.
compute BP_Min=min(BP1, BP2, BP3).
do repeat
BP=BP1, BP2, BP3/
Date=Date1, Date2, Date3/.
if BP=BP_Min Date_of_BP_Min=Date.
end repeat.
formats Date_of_BP_Min(edate10).
summarize BP1, BP2, BP3, Date1, Date2, Date3,
BP_Min, Date_of_BP_Min /format=list /cells=none.
In case 1 BP1=60 is lowest and so in variable Date_of_BP_Min the 'first' date '17.06.2021' will appear; in case 2 BP3=50 is lowest and the third date will appear in variable Date_of_BP_Min (17.10.2017). In case 3 the second date (03.02.2015). In case 4 there is a tie. '50' is lowest and is both measured on 23.12.2022 and 04.10.2014. This particular syntax will then 'choose' the 'last' date in the list, in this case 04.10.2014. This program can be used for any number of pairs of BP en/or Dates, there is no limit in the number of variables in the 'do repeat'-structure in SPSS. Other programs will call this same structure working with 'arrays', but the idea is the same, naturally.
Success, Ernest (TNO Leiden)
I see that this question is marked as Solved, but will offer another approach nevertheless. If you restructure the data from WIDE to LONG, you can use AGGREGATE to pick out the max BP. You can then set DateMaxBP = Date on any row where BP = MaxBP. And finally, you can use AGGREGATE again to write either the MIN or MAX value of DateMaxBP to every row for a given ID. Here is an example using the same data Ernest provided, but notice that I changed the data on the last row so that BP=150 occurs twice. HTH.
*** Start of syntax ***.
NEW FILE.
DATASET CLOSE ALL.
DATA LIST LIST/ BP1 to BP3 (3F5.0) Date1 (EDATE) Date2 (EDATE) Date3 (EDATE).
BEGIN DATA
60 80 90 17.06.2021 01.07.2020 15.09.2019
100 90 50 03.08.2020 01.09.2018 17.10.2017
110 40 100 02.04.2016 03.02.2015 07.12.2015
150 50 150 03.07.2014 23.12.2022 04.10.2014
END DATA.
* Generate a sequential ID code.
COMPUTE ID = $CASENUM.
FORMATS ID (F5.0).
DESCRIPTIVES ID.
* Restructure data from WIDE to LONG.
VARSTOCASES
/MAKE BP FROM BP1 TO BP3
/MAKE Date FROM Date1 TO Date3
/INDEX=i(3)
/KEEP=ID
/NULL=KEEP.
* Write MAX BP to a new variable.
AGGREGATE
/OUTFILE=* MODE=ADDVARIABLES OVERWRITE=YES
/BREAK=ID
/maxBP=MAX(BP).
NUMERIC DateMaxBP (EDATE11).
IF BP EQ maxBP DateMaxBP = Date.
LIST.
* Write DateMaxBP to every row for each ID.
* Max BP could occur on 2 or more dates.
* The code below records both the first and last
* dates on which max BP occurred.
AGGREGATE
/OUTFILE=* MODE=ADDVARIABLES OVERWRITE=YES
/BREAK=ID
/FirstDateMaxBP=MIN(DateMaxBP)
/LastDateMaxBP=MAX(DateMaxBP).
LIST.
DELETE VARIABLES DateMaxBP.
* Convert back to WIDE format (if necessary).
CASESTOVARS
/ID=ID
/INDEX=i
/SEPARATOR=""
/GROUPBY=VARIABLE.
LIST.
*** End of syntax ***.
- Badges
- Science topic
- Similar topics
- Filing