Help using Base SAS procedures

RE: Modifying

Accepted Solution Solved
Reply
Regular Contributor
Posts: 222
Accepted Solution

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:

CLIENTPRODUCTNUMBER
Janea1
Janeb2
Janeb2
Angiea1
Angiea1
Angieb2
Angiec3
Lisac3
Lisab2

After:

CLIENTPRODUCTNUMBER
Janea1
Janeb5
Janeb5
Angiea1
Angiea1
Angieb5
Angiec3
Lisac3
Lisab2

Accepted Solutions
Solution
‎12-30-2014 01:29 PM
Respected Advisor
Posts: 3,141

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;

View solution in original post


All Replies
Solution
‎12-30-2014 01:29 PM
Respected Advisor
Posts: 3,141

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.

Respected Advisor
Posts: 3,141

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;

Good luck on your SAS adventure!

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

;

Regular Contributor
Posts: 222

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.

Respected Advisor
Posts: 3,141

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

Respected Advisor
Posts: 3,989

Re: RE: Modifying

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

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