BookmarkSubscribeRSS Feed
grvsinghal
Calcite | Level 5
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
5 REPLIES 5
SPR
Quartz | Level 8 SPR
Quartz | Level 8
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
Cynthia_sas
SAS Super FREQ
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
Ksharp
Super User
[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
grvsinghal
Calcite | Level 5
Thank you SPR, cynthia and Ksharp. Now I know how to use lag function, and my problem is solved. Thanks a lot
abbess
Calcite | Level 5

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 3256 views
  • 1 like
  • 5 in conversation