How to handle this data in sas?

Accepted Solution Solved
Reply
Contributor
Posts: 29
Accepted Solution

How to handle this data in sas?

Hi,

i have 1 data set containing

week Store_id Item_id Promo_id

19 101 352 0

20 101 355 22501

21 101 356 0

21 101 356 35621

22 101 357 42016

23 101 358 96421

23 101 358 0

24 101 375 0

25 101 359 25694

25 101 359 0

26 101 360 36992

26 101 360 0

27 101 361 0

So my output should be as shown below:

week Store_id Item_id Promo_id

20 101 355 22501

21 101 356 0

21 101 356 35621

22 101 357 42016

23 101 358 96421

23 101 358 0

25 101 359 25694

25 101 359 0

26 101 360 36992

26 101 360 0

Note: Here we have 2 situation to retrieve required result:

1) i required a data with promo_id having (0) and (with id Ex:22501)  for same week, same store_id, Same Item_id.

2) i also want to consider for individual weak, store_id, Item_id with Promo id having (with id Ex:22501) been present but never "0" Promo id to be considered.


How to go about?

Thanks,

Ganesh K


Accepted Solutions
Solution
‎07-03-2015 08:15 AM
Grand Advisor
Posts: 9,584

Re: How to handle this data in sas?

Code: Program

data have;
infile cards truncover expandtabs;
input week Store_id Item_id Promo_id Quantity ;
cards;
20 101 355 22501 7
20 101 355 0 3
21 101 356 0 5
21 101 356 35621 10
22 101 356 42016 4
23 101 358 96421 9
23 101 358 0 4
25 101 359 25694 6
25 101 359 0 12
26 101 360 36992 5
26 101 360 0 8
;
run;
proc sql;
create table want as
  select *,case when Promo_id=0 then 0
   else Quantity/sum(Quantity)
   end as Calculation_Required
   from have
   group by Store_id ,Item_id ;
quit;

View solution in original post


All Replies
Grand Advisor
Posts: 9,584

Re: How to handle this data in sas?

If I understood what you mean.

Code: Program

data have;
input week Store_id Item_id Promo_id;
cards;
19 101 352 0
20 101 355 22501
21 101 356 0
21 101 356 35621
22 101 357 42016
23 101 358 96421
23 101 358 0
24 101 375 0
25 101 359 25694
25 101 359 0
26 101 360 36992
26 101 360 0
27 101 361 0
;
run;
proc sql;
create table want as
  select *
   from have
   group by week ,Store_id ,Item_id
   having (count(*)=1 and Promo_id ne 0) or
   (count(*) gt 1 and sum(Promo_id=0) gt 0 and sum(Promo_id ne 0) gt 0) ;
quit;

Xia Keshan

Contributor
Posts: 29

Re: How to handle this data in sas?

Hi xia keshan,

Thank for help, code was executed successfully.

For same output file i require calculated field to be created

weekStore_idItem_idPromo_idQuantityCalculation_Required
20101355225017"=7/7+3"
20101355030
21101356050
211013563562110"=10/5+10+4"
22101356420164"=4/5+10+4"
23101358964219"=9/9+4"
23101358040
25101359256946"=6/6+12"
251013590120
26101360369925"=5/5+8"
26101360080

Note:

If Promo_id is "0" then Calculation_Required is also "0".

If Promo_id is having value (Ex:22501) then calculation_Required is calculated as above shown table.

Is this possible in sas/SQL?

Thanks,

Ganesh K

Solution
‎07-03-2015 08:15 AM
Grand Advisor
Posts: 9,584

Re: How to handle this data in sas?

Code: Program

data have;
infile cards truncover expandtabs;
input week Store_id Item_id Promo_id Quantity ;
cards;
20 101 355 22501 7
20 101 355 0 3
21 101 356 0 5
21 101 356 35621 10
22 101 356 42016 4
23 101 358 96421 9
23 101 358 0 4
25 101 359 25694 6
25 101 359 0 12
26 101 360 36992 5
26 101 360 0 8
;
run;
proc sql;
create table want as
  select *,case when Promo_id=0 then 0
   else Quantity/sum(Quantity)
   end as Calculation_Required
   from have
   group by Store_id ,Item_id ;
quit;
Contributor
Posts: 21

Re: How to handle this data in sas?

Hi Ganesh,

Below is the solution to your problem :

proc sql noprint;

  create table _C as select *,

ifn(promo_id ne 0, quantity/sum(quantity), 0) as _sum

  from

  (

  select * from have where promo_id ne 0

  union

  select s.* from

  (select * from have where promo_id eq 0) s

   inner join

  (select * from have where promo_id ne 0) n

   on n.week = s.week

   and n.store_id = s.store_id

   and n.item_id = s.item_id

      )

  group by week, store_id, item_id

  order by week, store_id, item_id, promo_id desc;

quit;

Contributor
Posts: 21

Re: How to handle this data in sas?

Hi Ganesh,

One more solution to your problem, using the same dataset Xia has created :

proc sql noprint;

       create table _C as select *

       from

       (

            select * from have where promo_id ne 0

            union

            select s.* from

                 (select * from have where promo_id eq 0) s

                  inner join

                 (select * from have where promo_id ne 0) n

                  on n.week = s.week

                  and n.store_id = s.store_id

                  and n.item_id = s.item_id

        )

       order by week, store_id, item_id, promo_id desc;

quit;

Hope this will help you. Smiley Happy

Br,Amit

☑ This topic is SOLVED.

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

Discussion stats
  • 5 replies
  • 302 views
  • 9 likes
  • 3 in conversation