how to consider Average Value for missing Prices?

Accepted Solution Solved
Reply
Contributor
Posts: 29
Accepted Solution

how to consider Average Value for missing Prices?

hi,

i have data set as shown below:

Item_IdCategoryItem_DescriptionDateAGSBCMX
23555212ChipsPotato Lays Chips 50 gms2/1/20152231
23555212ChipsPotato Lays Chips 50 gms3/1/201533
23555212ChipsPotato Lays Chips 50 gms5/1/20152532
23555212ChipsPotato Lays Chips 50 gms6/1/201527
23555212ChipsPotato Lays Chips 50 gms7/1/201522
35442011ChipsChilly Lays Chips 50 gms2/1/201534
35442011ChipsChilly Lays Chips 50 gms3/1/201531
35442011ChipsChilly Lays Chips 50 gms4/1/2015
35442011ChipsChilly Lays Chips 50 gms5/1/201532
35442011ChipsChilly Lays Chips 50 gms6/1/201534

My O/P is to be executed as shown below

Item_IdCategoryItem_DescriptionDateAGSBCMX
23555212ChipsPotato Lays Chips 50 gms2/1/20152231
23555212ChipsPotato Lays Chips 50 gms3/1/2015(24) (i.e. 22+25+27+22/4)33
23555212ChipsPotato Lays Chips 50 gms5/1/20152532
23555212ChipsPotato Lays Chips 50 gms6/1/201527(32) (i.e. 31+33+32/3)
23555212ChipsPotato Lays Chips 50 gms7/1/201522(32) (i.e. 31+33+32/3)
35442011ChipsChilly Lays Chips 50 gms2/1/201534
35442011ChipsChilly Lays Chips 50 gms3/1/201531
35442011ChipsChilly Lays Chips 50 gms4/1/2015(32.75) (i.e. 34+31+32+34/4)
35442011ChipsChilly Lays Chips 50 gms5/1/201532
35442011ChipsChilly Lays Chips 50 gms6/1/201534

How to do this?

Thanks,

Ganesh K


Accepted Solutions
Solution
‎07-09-2015 01:46 PM
Valued Guide
Posts: 858

Re: how to consider Average Value for missing Prices?

Here you go:

DATA have;

infile cards dsd;

informat Item_Id $8. Category $5.    Item_Description $24.    Date mmddyy10.;

format Item_Id $8. Category $5.    Item_Description $24.    Date mmddyy10.;

input Item_Id$    Category$    Item_Description$    Date    AGS    BC    MX;

cards;

23555212,Chips,Potato Lays Chips 50 gms,2/1/2015,22,,31

23555212,Chips,Potato Lays Chips 50 gms,3/1/2015,,,33

23555212,Chips,Potato Lays Chips 50 gms,5/1/2015,25,,32

23555212,Chips,Potato Lays Chips 50 gms,6/1/2015,27,,

23555212,Chips,Potato Lays Chips 50 gms,7/1/2015,22,,

35442011,Chips,Chilly Lays Chips 50 gms,2/1/2015,,34,

35442011,Chips,Chilly Lays Chips 50 gms,3/1/2015,,31,

35442011,Chips,Chilly Lays Chips 50 gms,4/1/2015,,,

35442011,Chips,Chilly Lays Chips 50 gms,5/1/2015,,32,

35442011,Chips,Chilly Lays Chips 50 gms,6/1/2015,,34,

;

run;

proc sql;

create table prep as

select *,avg(ags) as _avg_ags,avg(bc)as _avg_bc,avg(mx) as _avg_mx

from have

group by item_id

order by item_id,date;

data want;

set prep;

by item_id;

if missing(ags) then ags = _avg_ags;

if missing(bc) then bc = _avg_bc;

if missing(mx) then mx = _avg_mx;

drop _:;

run;

View solution in original post


All Replies
Solution
‎07-09-2015 01:46 PM
Valued Guide
Posts: 858

Re: how to consider Average Value for missing Prices?

Here you go:

DATA have;

infile cards dsd;

informat Item_Id $8. Category $5.    Item_Description $24.    Date mmddyy10.;

format Item_Id $8. Category $5.    Item_Description $24.    Date mmddyy10.;

input Item_Id$    Category$    Item_Description$    Date    AGS    BC    MX;

cards;

23555212,Chips,Potato Lays Chips 50 gms,2/1/2015,22,,31

23555212,Chips,Potato Lays Chips 50 gms,3/1/2015,,,33

23555212,Chips,Potato Lays Chips 50 gms,5/1/2015,25,,32

23555212,Chips,Potato Lays Chips 50 gms,6/1/2015,27,,

23555212,Chips,Potato Lays Chips 50 gms,7/1/2015,22,,

35442011,Chips,Chilly Lays Chips 50 gms,2/1/2015,,34,

35442011,Chips,Chilly Lays Chips 50 gms,3/1/2015,,31,

35442011,Chips,Chilly Lays Chips 50 gms,4/1/2015,,,

35442011,Chips,Chilly Lays Chips 50 gms,5/1/2015,,32,

35442011,Chips,Chilly Lays Chips 50 gms,6/1/2015,,34,

;

run;

proc sql;

create table prep as

select *,avg(ags) as _avg_ags,avg(bc)as _avg_bc,avg(mx) as _avg_mx

from have

group by item_id

order by item_id,date;

data want;

set prep;

by item_id;

if missing(ags) then ags = _avg_ags;

if missing(bc) then bc = _avg_bc;

if missing(mx) then mx = _avg_mx;

drop _:;

run;

Super User
Posts: 9,681

Re: how to consider Average Value for missing Prices?

Code: Program

DATA have;
infile cards dsd;
informat Item_Id $8. Category $5.   Item_Description $24.   Date mmddyy10.;
format Item_Id $8. Category $5.   Item_Description $24.   Date mmddyy10.;
input Item_Id$   Category$   Item_Description$   Date   AGS   BC   MX;
cards;
23555212,Chips,Potato Lays Chips 50 gms,2/1/2015,22,,31
23555212,Chips,Potato Lays Chips 50 gms,3/1/2015,,,33
23555212,Chips,Potato Lays Chips 50 gms,5/1/2015,25,,32
23555212,Chips,Potato Lays Chips 50 gms,6/1/2015,27,,
23555212,Chips,Potato Lays Chips 50 gms,7/1/2015,22,,
35442011,Chips,Chilly Lays Chips 50 gms,2/1/2015,,34,
35442011,Chips,Chilly Lays Chips 50 gms,3/1/2015,,31,
35442011,Chips,Chilly Lays Chips 50 gms,4/1/2015,,,
35442011,Chips,Chilly Lays Chips 50 gms,5/1/2015,,32,
35442011,Chips,Chilly Lays Chips 50 gms,6/1/2015,,34,
;
run;

proc sql;
create table prep as
select Item_Id,Category,Item_Description,Date ,
   coalesce(ags,avg(ags)) as ags,
   coalesce(bc,avg(bc)) as bc,
   coalesce(mx,avg(mx)) as mx
from have
group by item_id
order by item_id,date;
quit;
☑ This topic is SOLVED.

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

Discussion stats
  • 2 replies
  • 212 views
  • 3 likes
  • 3 in conversation