Calculating percent change based on earliest to latest date variable

[kmardinian]

Hi, I have a fairly complicated issue that I have been trying to work and wanted to see whether anyone had a better way of going about this. I have a dataset that includes weight, systolic BP, and diastolic BP that has been collected on different days of the year. I would to calculate the percent change of the WEIGHT, SBP, and DBP variables at each previous date. I first created a count variable to order the dates from oldest date to newest date for each subject, and now I am trying to find a way to tell SAS to calculate the percent change for the weight, SBP, and DBP records for every date using the records from the date that comes before it. So COUNT 2-1, 3-2, 4-3, etc....

My dataset looks like this:

Subject          DATE                WEIGHT          SBP            DBP              COUNT

154              30NOV2016             102              120             80                     1

154              15DEC2016             102              115              75                     2

154               02JAN2017             110              120              80                     3

104               20SEP2016             145             120              80                     1

104               02JAN2017              110            120              80                      2

104               20FEB2017             145             120              80                     3

104               02MAR2017           110              120              80                      4

104               20APR2017             145             120              80                     5

Any help would be greatly appreciated! Thank you!

[PaigeMiller · Accepted solution]

data want;
    set have;
    by subject notsorted;
    prevsbp=lag(sbp);
    if not first.subject then percent_change_sbp=(sbp-prevsbp)/prevsbp;
run;

This is a simple example of how to do this for one of your variable. You can modify it obviously for your other variables.

[kmardinian]

Thank you so much! All of these options worked very well and I was able to apply them to all the variables.

[kmardinian]

One more question, if there were missing values in the weight column or any of the other columns, how would that affect the output?

[Reeza]

It would return a missing value and keeps moving forward meaning you'd have a gap in your measurements. You'll want to verify if after that missing is handled the way you need it to. If not, you can add some more conditional logic to handle that portion.

[PaigeMiller]

---

@kmardinian wrote:

One more question, if there were missing values in the weight column or any of the other columns, how would that affect the output?

---

SAS handles this properly!

[noling]

This is similar to @PaigeMiller but uses the retain instead:

data want;
	set have;
	retain prev_weight 8.;
	by subject notsorted;

	if not first.subject then do;
		*not 100% of calculation;
		percent_change_weight = (prev_weight - weight)/prev_weight *100;
	end;

	*set for next record;
	prev_weight = weight;
run;

[PGStats]

Or you could use SQL:

proc sql;
create table changes as
select 
    a.subject, a.date,
    (a.weight - b.weight)/b.weight as weightChange format=percentn6.1,    
    (a.sbp - b.sbp)/b.sbp as sbpChange format=percentn6.1,    
    (a.dbp - b.dbp)/b.dbp as dbpChange format=percentn6.1
from 
    have as a left join
    have as b on a.subject=b.subject and a.count = (b.count+1);
quit;

[Reeza]

One more variation 🙂

data want;
    set have;
    by subject date;

    *calculate percent change;
    pct_change = sbp/lag(sbp) -1;
  
    *set it to missing for first record since you dont have data yet;
    if first.subject then call missing(pct_change);
run;

---

@kmardinian wrote:

Hi, I have a fairly complicated issue that I have been trying to work and wanted to see whether anyone had a better way of going about this. I have a dataset that includes weight, systolic BP, and diastolic BP that has been collected on different days of the year. I would to calculate the percent change of the WEIGHT, SBP, and DBP variables at each previous date. I first created a count variable to order the dates from oldest date to newest date for each subject, and now I am trying to find a way to tell SAS to calculate the percent change for the weight, SBP, and DBP records for every date using the records from the date that comes before it. So COUNT 2-1, 3-2, 4-3, etc....

My dataset looks like this:

Subject          DATE                WEIGHT          SBP            DBP              COUNT

154              30NOV2016             102              120             80                     1

154              15DEC2016             102              115              75                     2

154               02JAN2017             110              120              80                     3

104               20SEP2016             145             120              80                     1

104               02JAN2017              110            120              80                      2

104               20FEB2017             145             120              80                     3

104               02MAR2017           110              120              80                      4

104               20APR2017             145             120              80                     5

Any help would be greatly appreciated! Thank you!

---