topic Re: how to retrieve the previous value to do the calculation in SAS Programming

how to retrieve the previous value to do the calculation

daisy6 — Wed, 07 Mar 2018 15:24:55 GMT

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;

but the code did not work well. Please help me.

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

RW9 — Wed, 07 Mar 2018 15:29:51 GMT

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;

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

daisy6 — Wed, 07 Mar 2018 15:58:10 GMT

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

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

mkeintz — Wed, 07 Mar 2018 16:19:43 GMT

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;

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

daisy6 — Wed, 07 Mar 2018 17:28:22 GMT

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