topic Re: Deleting records conditionally (before some value) in SAS Programming

Deleting records conditionally (before some value)

KalaBhairava — Sun, 14 Aug 2022 11:22:02 GMT

I have data like this and I want to flag or delete below highlighted records, i.e. value is 0 before 1 per each category and each subject.

Sub category date value
1 a 3/8/2022 0
1 a 3/9/2022 0
1 a 3/10/2022 1
1 c 3/11/2022 0
1 c 3/12/2022 1
2 a 3/8/2022 0
2 a 3/9/2022 1
2 a 3/10/2022 0
2 b 3/11/2022 1
2 b 3/12/2022 0
;

Re: Deleting records conditionally (before some value)

PaigeMiller — Sun, 14 Aug 2022 11:18:55 GMT

I'm not understanding the condition to create a flag. It seems like you want to flag all zeros. When would you not flag a zero? Please explain further.

Re: Deleting records conditionally (before some value)

KalaBhairava — Sun, 14 Aug 2022 11:22:40 GMT

Can you check Now, updated my question.

Re: Deleting records conditionally (before some value)

PaigeMiller — Sun, 14 Aug 2022 12:43:54 GMT

data intermediate;
    set have;
    by sub category;
    if first.category then count=0;
    count+1;
    if value=1 then one_location=count;
run;
proc summary data=intermediate nway;
	class sub category;
	var one_location;
	output out=_max_ max=max_one_location;
run;
data want;
    merge intermediate _max_(drop=_:);
    by sub category;
    if value=0 and count<max_one_location then delete;
run;

Re: Deleting records conditionally (before some value)

Astounding — Sun, 14 Aug 2022 13:34:02 GMT

When you come across a 0, you obviously don't know whether a 1 will appear later. So you have to go through the data twice. Here is one approach. The top loop goes through a sub/category combination and counts the 1's. Then the bottom loop examines the same observations and chooses which observations to output.

data want;
last1 = 0;
n_1 = 0;
Obsno = 0;
do until (last.category);
set have;
by sub category;
if value = 1 then last1 + 1;
end;
do until (last.category);
set have;
by sub category;
if value = 1 then n_1 + 1;
if value = 1 or n_1 < last_1 then output;
end;
run;

Working from a cell phone screen so I will need to look this over after I post it.

Re: Deleting records conditionally (before some value)

mkeintz — Sun, 14 Aug 2022 20:28:25 GMT

You apparently want to delete "leading zeroes" for each sub/category combination. If so then:

data want (drop=_:);
  set have;
  by sub category;
  if first.category=1 then _n_of_ones=value;
  else _n_of_ones+value;
  if _n_of_ones>0;
run;

This assumes that data are already sorted by sub/category (and presumably you require the data to be sorted by date within each sub/category). So assume data sorted by sub/category/date.

What you haven't explained is what you want to do if a given sub/category has no observations with value^=0. In such a case, the program above will drop all obs for that sub/category.

Additional comment:

You haven't answer @Kurt_Bremser 's question (or my comment above). I.e. what do you want to do if a given sub/category has only zeroes?

If you want to delete them all, then above program works. If you want to keep them all, then the following program will do:

data want (drop=_:);
  set have (where=(value=1) in=firstpass) have (in=secondpass);
  by sub category;
  if first.category then _cutoff_date=date;
  retain _cutoff_date;
  if secondpass and date>=_cutoff_date;
run;

If there are no 1's, then the _cutoff_date is the first value=0 date. But if there are some 1's, then it is the first value=1 date.

Re: Deleting records conditionally (before some value)

Kurt_Bremser — Sun, 14 Aug 2022 14:42:18 GMT

What if a sub has only zeroes?

Re: Deleting records conditionally (before some value)

KalaBhairava — Sun, 14 Aug 2022 15:57:56 GMT

It should be there, I just want to delete 0 before 1 per each subject per category.

i.e. if subject has 1 then only I want to delete 0 which are prior to 1, and 0 value at post 1 or only 0 shouldn't be affect.

Re: Deleting records conditionally (before some value)

Kurt_Bremser — Sun, 14 Aug 2022 16:39:12 GMT

You have not answered my question: what if a sub has only 0's and no 1 at all? Should all observations of such a group be deleted or kept?

Re: Deleting records conditionally (before some value)

KalaBhairava — Mon, 15 Aug 2022 04:26:49 GMT

It should be there

Re: Deleting records conditionally (before some value)

Kurt_Bremser — Mon, 15 Aug 2022 04:57:10 GMT

So we need an additional check for the "non-1" cases.

data want;
set have;
by sub;
if _n_ = 1
then do;
  declare hash w1 (dataset:"have (where=(value = 1))");
  w1.definekey("sub");
  w1.definedone(),
end;
retain keep_flag;
if first.sub
then if w1.check() ne 0
  then keep_flag = 1;
  else keep_flag = 0;
if value = 1 then keep_flag = 1;
if keep_flag;
drop keep_flag;
run;

Re: Deleting records conditionally (before some value)

Ksharp — Mon, 15 Aug 2022 12:45:10 GMT

data have;
input Sub  category  $ date   : $20.        value;
cards;
1          a            3/8/2022        0
1          a            3/9/2022        0
1          a            3/10/2022      1
1          c            3/11/2022      0
1          c            3/12/2022      1
2          a            3/8/2022        0
2          a           3/9/2022        1
2          a            3/10/2022      0
2          b            3/11/2022     1
2          b            3/12/2022      0
; 

data want;
 set have;
 by  Sub  category;
 retain found;
 if first.category then found=0;
 if value then found=1;
 if  found;
 drop found;
run;