## how to retrieve the previous value to do the calculation

Solved
Frequent Contributor
Posts: 102

# how to retrieve the previous value to do the calculation

Hello,

I have some data in which each examee may take several level exams many times. The data like

data test;

input id \$4. examlevel \$1. examdate \$10. grade \$4. times 2;

datalines;

id1  A 4/20/2013 Fail 3

id1  A  2/23/2014  Fail 5

id1  A  5/18/2015  Pass 8

id1  B  10/12/2013 Fail 2

id1  B 6/20/2014  Fail   4

id1  B 7/10/2015  Fail 6

id1  B 2/12/2016  Pass 8

......

Because data are missing or somebody did the wrong calculation. The exam times are not correct. I need to calculation how many times the examee took for each examlevel but I can not change those data before 12/31/2014, i.e.  I need the results like belows:

id1  A 4/20/2013 Fail 3

id1  A  2/23/2014  Fail 5

id1  A  5/18/2015  Pass 6

id1  B  10/12/2013 Fail 2

id1  B 6/20/2014  Fail   4

id1  B 7/10/2015  Fail 5

id1  B 2/12/2016  Pass 6

................

Part of my code is

proc sort data=test;
by id examlevel examdate;
run;

data want;
set test;
by id examlevel;
if date<=20088(12/31/2014) or first.id and first.examlevel then attempt=times;
else attempt=lag(times)+1;
run;

Accepted Solutions
Solution
‎03-07-2018 12:28 PM
Posts: 1,394

## Re: how to retrieve the previous value to do the calculation

You want to leave TIMES unchanged for dates <='31dec2014'd, but want to increment times by 1 for all later dates within an ID1/examlevel group.

Thank you for providing a data step for TEST, but you should have tested it before submitting it to the forum.  In particular, reading the examdate as a character variable would defeat any attempt to solve your request without first converting it to a sas date value.  There were also error messages generated by the input statement.

This is a task where RETAINing the prior value of TIMES is beneficial.  I put the retained value in variable _TIMES.

``````data test;
input id \$4. examlevel :\$1. examdate :mmddyy10. grade :\$4. times :2.;
datalines;
id1  A 4/20/2013 Fail 3
id1  A  2/23/2014  Fail 5
id1  A  5/18/2015  Pass 8
id1  B  10/12/2013 Fail 2
id1  B 6/20/2014  Fail   4
id1  B 7/10/2015  Fail 6
id1  B 2/12/2016  Pass 8
run;

data want (drop=_:);
set test;
by id examlevel;
retain _times;
if examdate<='31dec2014'd then _times=times;
else if first.examlevel=0 then do;
times=_times+1;
_times=times;
end;
run;
``````

All Replies
Super User
Posts: 9,840

## Re: how to retrieve the previous value to do the calculation

You will need to clarify your logic for me.  Why does the third row change and where does the 6 come from?  Note you can't use lag in this instance because you are changing the lag() value.  You would need to use a retained variable, e.g

```data want;
set have;
retain lsttimes;
by id examlevel;
if first.examlevel then lsttimes=times;
else do;
if...then times=lsttimes-2;
end;
run;```
Frequent Contributor
Posts: 102

## Re: how to retrieve the previous value to do the calculation

Thanks for reply. The third row times variable changes because of former employee's mistakes and I can not change the those data because it is in the systems. What I can do is the make the correct times after 2014 based on the times of examees took

Solution
‎03-07-2018 12:28 PM
Posts: 1,394

## Re: how to retrieve the previous value to do the calculation

You want to leave TIMES unchanged for dates <='31dec2014'd, but want to increment times by 1 for all later dates within an ID1/examlevel group.

Thank you for providing a data step for TEST, but you should have tested it before submitting it to the forum.  In particular, reading the examdate as a character variable would defeat any attempt to solve your request without first converting it to a sas date value.  There were also error messages generated by the input statement.

This is a task where RETAINing the prior value of TIMES is beneficial.  I put the retained value in variable _TIMES.

``````data test;
input id \$4. examlevel :\$1. examdate :mmddyy10. grade :\$4. times :2.;
datalines;
id1  A 4/20/2013 Fail 3
id1  A  2/23/2014  Fail 5
id1  A  5/18/2015  Pass 8
id1  B  10/12/2013 Fail 2
id1  B 6/20/2014  Fail   4
id1  B 7/10/2015  Fail 6
id1  B 2/12/2016  Pass 8
run;

data want (drop=_:);
set test;
by id examlevel;
retain _times;
if examdate<='31dec2014'd then _times=times;
else if first.examlevel=0 then do;
times=_times+1;
_times=times;
end;
run;
``````
Frequent Contributor
Posts: 102

## Re: how to retrieve the previous value to do the calculation

Thank you very much Mkeintz! That is what I want.

☑ This topic is solved.