topic Re: RE: Modifying in SAS Procedures

RE: Modifying

twildone — Tue, 30 Dec 2014 17:30:09 GMT

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

Re: RE: Modifying

Haikuo — Tue, 30 Dec 2014 18:29:14 GMT

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

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;

Re: RE: Modifying

MaikH_Schutze — Tue, 30 Dec 2014 18:37:03 GMT

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,

Re: RE: Modifying

Haikuo — Tue, 30 Dec 2014 18:57:37 GMT

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

Re: RE: Modifying

MaikH_Schutze — Tue, 30 Dec 2014 19:02:10 GMT

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,

Re: RE: Modifying

naveen_srini — Tue, 30 Dec 2014 19:30:38 GMT

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

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;

;

Re: RE: Modifying

twildone — Wed, 31 Dec 2014 01:09:16 GMT

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.

Re: RE: Modifying

Haikuo — Wed, 31 Dec 2014 01:26:45 GMT

'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

Re: RE: Modifying

Patrick — Wed, 31 Dec 2014 01:27:37 GMT

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