topic Re: Replace zeros after first zero for each group in SAS Programming

Replace zeros after first zero for each group

pavank — Fri, 11 Oct 2024 16:28:18 GMT

using below dataset and get below output data sc;
input group $ id ;
datalines;
A 1
A 1
A 0
A 1
A 1
B 2
B 0
B 2
B 2
C 3
C 0
C 3
C 3
;
run;

/*output*/
Name id
A 1
A 1
A 0
A 0
A 0
B 2
B 0
B 0
B 0
C 3
C 0
C 0
C 0

Re: Replace zeros after first zero for each group

JOL — Fri, 11 Oct 2024 17:03:26 GMT

data test;
input group $ id;
datalines;
A 1
A 1
A 0
A 1
A 1
B 2
B 0
B 2
B 2
C 3
C 0
C 3
C 3
;
run;

data new;
set test;
by group;
retain id2;

if first.group=1 then
id2 = .;

if first.group=0 and id=0 then
do;
id2=0;
id=id2;
end;

if id2=. then
id3 = id;
else id3=id2;
keep group id3;
run;

Re: Replace zeros after first zero for each group

ballardw — Fri, 11 Oct 2024 17:15:05 GMT

If the actual goal is "set all values of Id to 0 within a group after the first 0" then this is one way:

data want;
  set have;
  by group;
  retain replaceflag;
  if first.group then replaceflag=0;
  if id=0 then replaceflag=1;
  if replaceflag then id=0;
  drop replaceflag;
run;

How this works:

When you use a BY group in a data step each variable on the BY statement has temporary (not written to the data set) variables First.var and Last.var that indicate whether the current observation is the first or last of the group. The values are 1, true, and 0, false, so can be used to test when group changes.

RETAIN means keep values of the variable across the data step boundary.

So we set the Replaceflag to 0 at the start of the group. When the first 0 is encountered we change the flag to 1 (true).

So if Replaceflag is true set the value of Id to 0.

Redundant a bit for the first Id=0 but easier to do than to try to not set an already existing value of 0 to 0.

Drop the Replaceflag from the output data set as likely not needed later.

Note: You did not actually provide rules and your subject line is poorly stated.

It also does not provide any idea if MISSING values should be assigned 0 or not. The above code will assign missing Id to 0 after the Replaceflag is set.

@pavank wrote:
using below dataset and get below output data sc;
input group $ id ;
datalines;
A 1
A 1
A 0
A 1
A 1
B 2
B 0
B 2
B 2
C 3
C 0
C 3
C 3
;
run;

/*output*/
Name id
A 1
A 1
A 0
A 0
A 0
B 2
B 0
B 0
B 0
C 3
C 0
C 0
C 0

Re: Replace zeros after first zero for each group

pavank — Sat, 12 Oct 2024 05:42:34 GMT

Hi @ballardw ,

Thank you very much for your solution I thought COALESCE function works here

Re: Replace zeros after first zero for each group

ballardw — Sat, 12 Oct 2024 12:45:55 GMT

@pavank wrote:

Hi @ballardw ,

Thank you very much for your solution I thought COALESCE function works here

Since Coalesce, or Coalescec, involve selecting the first non-missing value from a list of values I am not sure where you are thinking of applying that function. None of your observations showed a missing value for any of the variables.

Re: Replace zeros after first zero for each group

FreelanceReinh — Sat, 12 Oct 2024 17:20:22 GMT

@pavank wrote:

Hi @ballardw ,

Thank you very much for your solution I thought COALESCE function works here

Hi @pavank,

Interesting that you ask about the COALESCE function because I used that yesterday for an alternative to JOL's solution. But then I saw that ballardw had suggested a quite similar DATA step in the meantime and so I didn't post mine.

Here it is:

data want(drop=_);
set sc;
by group;
if id=0 then _+0;
else if first.group then _=.;
else id=coalesce(_,id);
run;

(Note that the underscore is a valid variable name -- one that is rather unlikely to collide with a name of one of your existing variables.)