DATA Step, Macro, Functions and more

Removing/Deleting Observation and increment another variable of the group

Reply
Contributor
Posts: 71

Removing/Deleting Observation and increment another variable of the group

Hello Everyone,

 

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

 

SnoPH
1A0
1A1
1S0
1D0
2S1
2A0
3S0
3Q1
4X0
5W1
6A0
6B0
7A1
7S1
7D0

 

Output

SnoPH
1A1
1S0
1D0
2A1
3S1
4X0
6A0
6B0
7D2

 

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.

 

Super Contributor
Posts: 305

Re: Removing/Deleting Observation and increment another variable of the group

Hello,

 

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

Super User
Posts: 9,687

Re: Removing/Deleting Observation and increment another variable of the group

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

Frequent Contributor
Posts: 104

Re: Removing/Deleting Observation and increment another variable of the group

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;
________________________

- That still only counts as one -

Contributor
Posts: 71

Re: Removing/Deleting Observation and increment another variable of the group

@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. 

Super User
Posts: 9,687

Re: Removing/Deleting Observation and increment another variable of the group

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;

Ask a Question
Discussion stats
  • 5 replies
  • 254 views
  • 1 like
  • 4 in conversation