## RE: Modifying

Solved
Regular Contributor
Posts: 247

# RE: Modifying

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

Accepted Solutions
Solution
‎12-30-2014 01:29 PM
Posts: 3,167

## Re: RE: Modifying

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;

All Replies
Solution
‎12-30-2014 01:29 PM
Posts: 3,167

## Re: RE: Modifying

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;

Contributor
Posts: 45

## Re: RE: Modifying

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.

Posts: 3,167

## Re: RE: Modifying

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;

Haikuo

Contributor
Posts: 45

## Re: RE: Modifying

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.

Frequent Contributor
Posts: 115

## Re: RE: Modifying

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;

;

Regular Contributor
Posts: 247

## Re: RE: Modifying

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.

Posts: 3,167

## Re: RE: Modifying

'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

Posts: 4,736