RE: Modifying

[twildone]

Hi.....I am trying to make changes to field named "Number" within or for each Client (grouped by client) only and only if for each client, the Product ='a' is listed and if Product 'a' is part of the subset for any particular client, then I would like to change the field Number associated to Product = 'b' to 5. That is, the only time that Product ='b' would retain the Number = 2 is if Product ='a' is not part of the subset or list for that client. Hopefully, the tables below will illustrates what I am trying to achieve. Thanks in advance.

Before:

CLIENT	PRODUCT	NUMBER
Jane	a	1
Jane	b	2
Jane	b	2
Angie	a	1
Angie	a	1
Angie	b	2
Angie	c	3
Lisa	c	3
Lisa	b	2

After:

CLIENT	PRODUCT	NUMBER
Jane	a	1
Jane	b	5
Jane	b	5
Angie	a	1
Angie	a	1
Angie	b	5
Angie	c	3
Lisa	c	3
Lisa	b	2

[Haikuo · Accepted solution]

There would be many other approaches, here are two of them to start the conversation:

data have;

     input (CLIENT   PRODUCT) (:$8.) NUMBER;

     cards;

Jane a    1

Jane b    2

Jane b    2

Angie a    1

Angie a    1

Angie b    2

Angie c    3

Lisa c    3

Lisa b    2

;

/*DOW*/

data want_dow;

     do until (last.client);

           set have;

           by client notsorted;

           if product='a' then

                flag=1;

     end;

     do until (last.client);

           set have;

           by client notsorted;

           if product='b' and flag=1 then

                number=5;

           output;

     end;

     drop flag;

run;

/*Proc SQL*/

proc sql;

     create table want_sql as

           select client, product,

                case

                     when sum(product='a')>0 and product='b' then 5

                     else number

                end

           as number

                from have

                     group by client;

quit;

[MaikH_Schutze]

Hi,

I am following this discussion and was trying to find a DATA step solution myself because I am not too familiar with SQL.

Also, I only recently came across the DOW loop. If you don't mind, can you please provide a brief description of your solution?

Thanks,

M.

[Haikuo]

It requires 2XDOW in this case. The first DOW tested product='a', if yes, then flag=1, the value of the flag will then be carried on to the second DOW, where product='b' is tested, and if yes, then corresponding number was converted to 5, per requirement. DOW is just one of the ways data step spin data, it is powerful and flexible. But for this particular case, the following code will do the same:

/*stack*/

proc sort data=have;

     by client;

run;

data want_stk;

     set have (in=up) have(in=down);

     by client;

     retain flag;

     if first.client then

           flag=.;

     if product='a' then

           flag=1;

     if flag=1 and product='b' then

           number=5;

     if down then

           output;

     drop flag;

run;

Good luck on your SAS adventure!

Haikuo

[MaikH_Schutze]

Excellent. I am very interested in the DOW technique but haven't invested the time to study it. I literally just learned of it earlier this week.

I was trying to use the retain statement myself and I am glad you have an example using it as well.

Thanks,

M.

[naveen_srini]

Haikuo's codes are classy and expert level, but if you are learner like me, you can join my club with simple code below, let's learn together from haikuo

data have;/*copied haikuo's raw data*/

     input (CLIENT   PRODUCT) (:$8.) NUMBER;

     cards;

Jane a    1

Jane b    2

Jane b    2

Angie a    1

Angie a    1

Angie b    2

Angie c    3

Lisa c    3

Lisa b    2

data want;

set have;

by client product notsorted;

retain n;

if  first.client and first.product then n=product;

else if  not first.client and product="b" and n="a" then number=5;

drop n;

run;

;

[twildone]

Perfect!!!!! Thank You so much Hai.kuo. I tried both methods and both worked perfectly. Although I haven't tried Naveen_srini suggestion and code, I agree it looks a lot simpler and I am sure it also would work. Thank You once again for everyones suggestions and comments.

[Haikuo]

's solution works IF your product='a' (if there is one) always occur before product='b'. Make sure if that is the case (you can presort your data to meet this criteria if you may), then you can go right ahead.

Good luck,

Haikuo

[Patrick]

Then please mark helpful answers as "helpful" and the answer you like the most as "correct".