SAS: Identify Row Level Changes and Tagging

Sort the dataset first and identify the change using lag(var).
The example below shows if the student cumulative credits drops from previous term, the count of degree will increase by 1.

/* identify the row observation that the change took place */
data data2;
set data1;
by sisid term; 
if first.sisid then deg =1;
else if sisid = lag1(sisid) and cumm_cr < lag1(cumm_cr) then deg = 2;
run;
/* tagging the observations associated with the change */
data data3 ;
set data2;
by sisid;
retain temp;
if first.sisid then do; temp = 0; end;
if deg ne . then temp = deg;
else if deg eq . then deg= temp;
run;

Updated to

data data2;
set data1;
by sisid term; 
retain deg;
if first.sisid then deg =1;
else if sisid = lag1(sisid) and cumm_cr < lag1(cumm_cr) then deg = deg +1;
run;

SAS: Update values with the last record and keep other attributes of the first record by group

The list contains course grades for students and the year students took the course.  It is possible that students can retake the course to improve their gpa.  We want a list that the last records of the students are showing all the grades that they took in the past and if a course was taken multiple times then the most recenct grade of that course.  We also want the last records of the student to show the initial year that the students were taking these course.

The following marco used a temp variable to hold the grade of the course and retain the value for the same student and if the course is retaken, the temp variable will be assigned with new grade, and if the course grade is empty, then the value retained in the  temp variable will be assigned to the course grade.

%macro lst (in =, out=, crs=);
data &out.;
drop temp ;
set &in.;
by id;
length temp $2 firstcrsyr $4 ;
retain temp firstcrsyr ;
if first.id then do; temp = '' ; firstcrsyr = yr; end;
/* Assign TEMP when courseX is non-missing /
if &crs. ne '' then temp=&crs.;
/ When X is missing, assign the retained value of TEMP into courseX */
else if &crs. eq '' then &crs.=temp;
run;
%mend lst;
%lst (in = list1, out = list2, crs= course1);
%lst (in = list2, out = list2, crs= course2);
%lst (in = list2, out = list2, crs= course3);

SAS: Assign Order Number by group and Calculating Accumulative Total in dataset using Retain Statement

Assigning group order for a set sequence of records.

data temp2;
retain order;
set temp1;
if _n_ =1 then order=1; /* _n_=1 identifies the first record.
else if var1 ne lag(var1) then order= sum(order,1);
else order = order ; 
run;

• Lag(var) the value of previous record in the var column.
• retain statement needs to be before the set statement.

Calculating the running total of weight and height for each record. The Retain statement retains the running total from the previous iteration to the next.

data one;
length AccWeight AccHeight 8.;
retain AccWeight 0 AccHeight 0;
set sashelp.class end=eof;
AccWeight = sum(AccWeight, weight);
AccHeight = sum(AccHeight, Height);
run;

The following codes deal with cumulative sum by the group where the variable for calculation contains null values. The variable regs is either 1 or 0 or null value. Sorting by the regs variable will force the the record with a regs value of 1 to be last record by the desired group.

Proc sort data= apps out = sortapp;
by id app_num regs;
run;
data sortapp1;
set sortapp;
by id app_num;
retain sumreg;
firstnum = first.a_num;
lastnum = last.a_num;
if first.a_num then sumreg = sum(regs);
else sumreg = sum(sumreg, regs);
run;