topic Re: Calculating percent change based on earliest to latest date variable in SAS Programming

Calculating percent change based on earliest to latest date variable

kmardinian — Tue, 21 May 2019 17:49:03 GMT

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!

Re: Calculating percent change based on earliest to latest date variable

PaigeMiller — Tue, 21 May 2019 17:55:54 GMT

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.

Re: Calculating percent change based on earliest to latest date variable

noling — Tue, 21 May 2019 18:05:18 GMT

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;

Re: Calculating percent change based on earliest to latest date variable

PGStats — Tue, 21 May 2019 18:18:34 GMT

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;

Re: Calculating percent change based on earliest to latest date variable

Reeza — Tue, 21 May 2019 18:17:34 GMT

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!

Re: Calculating percent change based on earliest to latest date variable

kmardinian — Tue, 21 May 2019 20:15:40 GMT

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

Re: Calculating percent change based on earliest to latest date variable

kmardinian — Tue, 21 May 2019 20:26:47 GMT

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

Re: Calculating percent change based on earliest to latest date variable

Reeza — Tue, 21 May 2019 20:31:24 GMT

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.

Re: Calculating percent change based on earliest to latest date variable

PaigeMiller — Wed, 22 May 2019 13:00:19 GMT

@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!