BookmarkSubscribeRSS Feed
deega
Quartz | Level 8

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.

 

5 REPLIES 5
Loko
Barite | Level 11

Hello,

 

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

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

Oligolas
Barite | Level 11

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

- Cheers -

deega
Quartz | Level 8

@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
Super User
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;

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • 1117 views
  • 1 like
  • 4 in conversation