topic Re: Changing value of previous observation in SAS Programming

Changing value of previous observation

grvsinghal — Fri, 10 Jun 2011 12:08:03 GMT

This is the dataset I have
ID Status
1 A
1 A
1 Z
1 Z
1 A
1 Z
2 A
2 A
2 Z
2 Z

If status for any particular ID changes from Z to A, I want to update its value as A in all previous observations of that ID
So this is what i want after processing

1 A
1 A
1 A
1 A
1 A
1 Z
2 A
2 A
2 Z
2 Z

I tried this
data c;
set b;
by id;
if status = 'A' and lag(status) = 'Z' then lag(status)='A';
run;

But I get a dataset C with 0 observations. My original dataset B can have thousands of IDs and for some of them status may change from Z to A, so for all such IDs, I want to update status from Z to A as shown above

Re: Changing value of previous observation

SPR — Fri, 10 Jun 2011 19:52:21 GMT

Hello Grvsinghal,

This is a possible solution:
[pre]
data i;
input ID Status $;
datalines;
1 A
1 A
1 Z
1 Z
1 A
1 Z
2 A
2 A
2 Z
2 Z
run;
proc transpose data=i out=t prefix=s;
var status;
by id;
run;
data r0;
set t;
array ss {*} s:;
do i=1 to DIM(ss)-1;
if ss[i]="Z" and ss[i+1]="A" then
do j=1 to i;
ss[j]="A";
end;
end;
by id;
run;
proc transpose data=r0 out=r(drop=_:);
var s:;
by ID;
run;
[/pre]
Sincerely,
SPR

Re: Changing value of previous observation

Cynthia_sas — Fri, 10 Jun 2011 19:59:04 GMT

Hi:
This Tech Support note outlines some of the problems you might encounter when you call a LAG function conditionally. (inside an IF condition) http://support.sas.com/kb/24/694.html The note says:
"A LAGn function that is executed conditionally will store and return values only from the observations for which the condition is satisfied. " This means that the LAG queue/stack might not contain the values you expect if LAG is not called for every observation.

And this note shows the WRONG and RIGHT ways to use the LAG function:
http://support.sas.com/kb/24/665.html

cynthia

Re: Changing value of previous observation

Ksharp — Mon, 13 Jun 2011 08:09:24 GMT

[pre]
data i;
input ID Status $;
count+1;
datalines;
1 A
1 A
1 Z
1 Z
1 A
1 Z
2 A
2 A
2 Z
2 Z
;
run;
data temp(rename=(count=_count status=_status));
set i;
if id eq lag(id) and status='A' and lag(status)='Z';
run;
data want(drop=_: count);
merge i temp;
by id;
if count lt _count then status=_status;
run;
[/pre]

Ksharp

Re: Changing value of previous observation

grvsinghal — Mon, 13 Jun 2011 14:25:06 GMT

Thank you SPR, cynthia and Ksharp. Now I know how to use lag function, and my problem is solved. Thanks a lot

Re: Changing value of previous observation

abbess — Mon, 25 Jan 2016 17:01:31 GMT

Hello Grvsinghal,

You could try using call execute as below:

data b; set a; run;

data _null_;
  set a;
  by id;

  l_id=lag(id);
  l_status=lag(status);

  if id eq l_id and status eq 'A' and l_status='Z' then do;
    call execute('data b; set b;
                    if id eq '||id||' and _N_ le '||_N_||' and status="Z" then status="A";
                  run;');
  end;
run;