BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
twildone
Pyrite | Level 9

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:

CLIENTPRODUCTNUMBER
Janea1
Janeb2
Janeb2
Angiea1
Angiea1
Angieb2
Angiec3
Lisac3
Lisab2

After:

CLIENTPRODUCTNUMBER
Janea1
Janeb5
Janeb5
Angiea1
Angiea1
Angieb5
Angiec3
Lisac3
Lisab2
1 ACCEPTED SOLUTION

Accepted Solutions
Haikuo
Onyx | Level 15

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;

View solution in original post

8 REPLIES 8
Haikuo
Onyx | Level 15

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
Quartz | Level 8

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
Onyx | Level 15

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
Quartz | Level 8

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
Quartz | Level 8

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 haikuoSmiley Happy

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
Pyrite | Level 9

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
Onyx | Level 15

'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
Opal | Level 21

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 1350 views
  • 11 likes
  • 5 in conversation