Conditionally delete the most recently inserted observation in SAS

Question

I have two tables A and B that look like below.

Table A

rowno flag1 flag2 flag3 1 1 0 0 2 0 1 1 3 0 0 0 4 0 1 1 5 0 0 1 6 0 0 0 7 0 0 0 8 0 1 0 9 0 0 0 10 1 0 0

Table B

rowno flag1 flag2 flag3

Table A and B have the same column names but B is an empty table initially.

So what I want to accomplish is to insert the values from A to B row by row using macro, iteration by rowno. And each time I insert one row from A to B, I want to calculate the sum of each flag column.

If after insert each row, the sum(flag1) > 1 or sum(flag2) >1 or sum(flag3) >1, I need to delete that inserted row from table B. Then the iteration keeps running till the end of the observation in Table A. The final output in Table B is to have 5 observations from table A.

the code I have so far is below:

%macro iteration;

%do rowno=1 %to 10;

proc sql;

insert into table.B
select *
from table.A
where rowno = &rowno;

quit;

set table.B;

if
sum(flag1) > 1
or
sum(flag2) > 1
or
sum(flag3) > 1

then delete;

run;

%end;
%mend iteration;

%iteration

I received a lot of error messages.

Looking forward to your help and suggestions. Thanks.

The ideal output data would look like this

 rowno  flag1  flag2  flag3
    1    1      0      0
    2    0      1      1
    3    0      0      0
    6    0      0      0
    7    0      0      0

Answer 1

Instead of a macro, use a running sum to calculate the running sum of each row. If you need to delete a row remember to reverse the increment to the running sum. Based on your data, I think Row 9 should also be kept.

data TableA;
input rowno  flag1  flag2  flag3;
cards;
    1    1      0      0
    2    0      1      1
    3    0      0      0
    4    0      1      1
    5    0      0      1
    6    0      0      0
    7    0      0      0
    8    0      1      0
    9    0      0      0
   10    1      0      0
;
run;

data TableB;
set TableA;
retain sum_:;

*Increment running sum for flag;
sum_flag1+flag1;
sum_flag2+flag2;
sum_flag3+flag3;

*Check flag amounts;
if sum_flag1>1 or sum_flag2>1 or sum_flag3>1 then do;
    *if flag is tripped then delete increment to flag and remove record;
    sum_flag1 +-flag1;
    sum_flag2 +-flag2;
    sum_flag3 +-flag3;
    delete;
end;

run;