topic Re: Select rows before and after a condition in SAS Programming

Select rows before a condition

Yuna11 — Wed, 02 Oct 2024 14:47:25 GMT

/*In my study, I want to flag all the rows before 1 by each group. 
They are already sorted by time, and each group will have 9 records and will have and only have one "1" marked, and I want to select _n_ < rownum(when x=1) */

Data have;
  INPUT Group $ ID X;
  DATALINES;
A 01 0
A 02 0
A 03 0
A 04 0
A 05 1
A 06 0
A 07 0
A 08 0
A 09 0
B 01 0
B 02 0
B 03 0
B 04 0
B 05 0
B 06 0
B 07 0
B 08 1
B 09 0
;

Data want;
INPUT Group $ ID X Y;
DATALINES;
A 01 0 Y
A 02 0 Y
A 03 0 Y
A 04 0 Y
A 05 1 Y
A 06 0 
A 07 0 
A 08 0 
A 09 0 
B 01 0 Y
B 02 0 Y
B 03 0 Y
B 04 0 Y
B 05 0 Y
B 06 0 Y
B 07 0 Y
B 08 1 Y
B 09 0 
;

Re: Select rows before and after a condition

Tom — Wed, 02 Oct 2024 14:34:26 GMT

What is "row index number"?

Why not just start the value as Y and then change it to N after you see the first one? That works for your two example groups.

data want;
  set have ;
  by group x notsorted;
  if first.group then y='Y';
  else if first.x and x=0 then y='N';
  retain y;
run;

But what do you want to do with groups that never have a one?

What about groups that have multiple observations with a one?

Re: Select rows before and after a condition

Yuna11 — Wed, 02 Oct 2024 14:40:56 GMT

Hi Tom, I just edited my question a little bit, hopefully it is more clear now. The data is sorted by time and each group will have a 1 and only one "1" either at middle of somewhere or at the end. And I want to flag the rows before 1.

Re: Select rows before and after a condition

ballardw — Wed, 02 Oct 2024 14:43:20 GMT

Please explain why if you want "also all the rows after 1 by each group. " that all the rows actually after the 1 are marked N in your "want" data set?

I don't see anything in your "want" that indicates a selection of "after". So you need to very clearly describe what that would look like.

In a general use of SAS aside: recommend use 1 for Yes/True/of a property or similar and 0 for No/False/not of a property if there are only two values. For one thing SAS treats 1/0 as true/false internally.

x = (a > b);

assigns values of 1 to x when a is greater than b. To use Y/N letters you have to use If/Then/Else or some other more complex code. It can also be much easier to report on the 1's without including the 0s by using Sum and mean(percent of 1's).

Re: Select rows before and after a condition

Tom — Wed, 02 Oct 2024 14:54:08 GMT

@Yuna11 wrote:
Hi Tom, I just edited my question a little bit, hopefully it is more clear now. The data is sorted by time and each group will have a 1 and only one "1" either at middle of somewhere or at the end. And I want to flag the rows before 1.

That is even easier.

data want;
  set have;
  by group ;
  if first.group then y='Y';
  if lag(x)=1 and not first.group then y='N';
  retain y;
run;

The reason there are two IF statements instead of just an ELSE is because you need to execute the LAG() function for every observation for it to work properly. The LAG() function remembers the values that are passed into it, so if you skip executing it for some observations then those values of X never make into the list of values that can be returned by the LAG() function.

You could also skip the LAG() function and instead insert an OUTPUT statement so you can change the value of Y after it has already been written to the output dataset.

data want;
  set have;
  by group ;
  if first.group then y='Y';
  retain y;
  output;
  if x=1 then y='N';
run;