topic Calculating Trend for Each Group of Observations in SAS Programming

Calculating Trend for Each Group of Observations

rj997 — Mon, 23 Mar 2020 22:33:58 GMT

Hey all!

I have a table that looks like this:

ID TERM CUR GPA

ABC 1 3.5

ABC 2 3.67

ABC 3 3.43

XYZ 1 2.67

XYZ 2 2.69

XYZ 3 3.01

I want to calculate the Percent change for each group of Student ID and then the trend for each term.

I have been trying to calculate the percent change for each student ID as:

a = lag(CUR_GPA)

change = ((current-a)/a)

mean = mean(change)

If I follow this the 'a' value for each first student ID will be 0 because I wish to group by each ID and do these calculations on particular groups.

Thank you

Re: Calculating Trend for Each Group of Observations

mkeintz — Mon, 23 Mar 2020 22:50:37 GMT

Do you want a trend, or do your want record-by-record changes? You code suggests the latter while you title suggest the former?

Please provide a example of how you want the results to look like.

Re: Calculating Trend for Each Group of Observations

rj997 — Mon, 23 Mar 2020 22:57:58 GMT

Hey! Thanks for replying.

I want to calculate the trend based on the record by record change i.e. a mean of the change.

Thanks

Re: Calculating Trend for Each Group of Observations

mkeintz — Tue, 24 Mar 2020 00:35:09 GMT

And I repeat, how do your want you output to look? Do you want a report, without saving a new dataset file? Do you want a dataset with one observation per id, containing a new average percent change? Something else?

Re: Calculating Trend for Each Group of Observations

rj997 — Tue, 24 Mar 2020 17:12:41 GMT

I am trying to create a new data set with one observation per id.

Thanks

Re: Calculating Trend for Each Group of Observations

mkeintz — Wed, 25 Mar 2020 21:58:22 GMT

You apparently want the average relative change within a group, not a trend as defined by a regression line slope. If so:

data have;
  input ID $3. TERM  CUR_GPA;
datalines;
ABC 1 3.5
ABC 2 3.67
ABC 3 3.43
XYZ 1 2.67
XYZ 2 2.69
XYZ 3 3.01
run;
data want (keep=id mean_dif);
  set have;
  by id;
  difsum+dif(cur_gpa);
  nid+1;
  if first.id then do;
     difsum=0;
     nid=1;
  end;
  if last.id;
  mean_dif=difsum/(nid-1);
run;