Removing/Deleting Observation and increment another variable of the group

[deega]

Hello Everyone,

As Data speaks better so please find below my input and desired output.

Sno	P	H
1	A	0
1	A	1
1	S	0
1	D	0
2	S	1
2	A	0
3	S	0
3	Q	1
4	X	0
5	W	1
6	A	0
6	B	0
7	A	1
7	S	1
7	D	0

Output

Sno	P	H
1	A	1
1	S	0
1	D	0
2	A	1
3	S	1
4	X	0
6	A	0
6	B	0
7	D	2

Actually, what I am trying to do is, if H=1 then I have delete that recors, thats simple but the problem is , how to increment it for other member of the group. Also, the name of the variable 'H' does not matter in the output, if its easier to have it with new name, then also its fine.

[Loko]

Hello,

how do you recognize the group , based on Sno? why for Sno=6 , A= ? shouldn't for this row A=2 ?

[Ksharp]

When sno=1 and you delete A 1,
Why A 0 --> A 1
Not S 0 -->  S 1  Or  D 0 -->  D 1 ?

[Oligolas]

Looks to me like the H you keep in your output dataset is the sum of the Hs in the input dataset, grouped by sno.

Afterward you remove the rows which had H=1 in the input dataset.

Only the first row in each sno group gets the sum of the Hs.

There is no 'increment'.

In this case a solution could look like this:

data have;
   infile cards expandtabs truncover;
   input Sno P $ H;
   cards;
1	A	0
1	A	1
1	S	0
1	D	0
2	S	1
2	A	0
3	S	0
3	Q	1
4	X	0
5	W	1
6	A	0
6	B	0
7	A	1
7	S	1
7	D	0
   ;
run;

data have;
   set have;
   order+1;
run;

proc sort data=have; by sno order; run;

data want;
   length buf 8;
   set have;
   by sno order;

   retain buf .;
   if first.sno then buf=0;
   buf=buf+h;
run;

proc sort data=want; by sno descending order; run;

data want1;
   set want;
   retain sum;
   by sno descending order; 
   if first.sno then sum=buf;
   if buf>sum then sum=buf;
run;

proc sort data=want1 out=want2; by sno order; run;

data want2;
   set want2;
   where h ne 1;
   by sno order;
   if first.sno then h=sum;
   keep sno p h;
run;

[deega]

@Ksharp

SNo is the group name/number which is constant and will not change, P are the products bought by SNo, now H is whther they liked the product or not. H=1 means they did not like the product so I want to delete that product but at the same time I want to keep the record of number of products they did not like, so I am incrementing H. H in the input is whther they like P or not and H in output is how many P`s they did not like. 

[Ksharp]

What I am saying is what P should be populated with this number of not like .


data have;
   infile cards expandtabs truncover;
   input Sno P $ H;
   cards;
1	A	0
1	A	1
1	S	0
1	D	0
2	S	1
2	A	0
3	S	0
3	Q	1
4	X	0
5	W	1
6	A	0
6	B	0
7	A	1
7	S	1
7	D	0
   ;
run;
proc sort data=have;by sno p;run;
data zero one;
 set have;
 if h=1 then output one;
  else output zero;
run;
data temp temp_sum;
 merge zero(in=ina) one(in=inb);
 by sno p;
if h=1 and not ina and inb then do;
 output temp_sum;
 delete;
end;
output temp;
run;
proc summary data=temp_sum;
by sno;
var h;
output out=sum(drop=_:) sum=sum_h;
run;
data want;
 merge temp(in=ina) sum;
 by sno;
 retain found 0;
 if first.sno then found=0;
 if h=0 and not found then do;
   if not missing(sum_h) then do;
    h=sum_h;
    found=1;
   end;
 end;
 if ina;
drop found sum_h;
run;