how to consider Average Value for missing Prices?

[Ganeshk]

hi,

i have data set as shown below:

Item_Id	Category	Item_Description	Date	AGS	BC	MX
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

My O/P is to be executed as shown below

Item_Id	Category	Item_Description	Date	AGS	BC	MX
23555212	Chips	Potato Lays Chips 50 gms	2/1/2015	22		31
23555212	Chips	Potato Lays Chips 50 gms	3/1/2015	(24) (i.e. 22+25+27+22/4)		33
23555212	Chips	Potato Lays Chips 50 gms	5/1/2015	25		32
23555212	Chips	Potato Lays Chips 50 gms	6/1/2015	27		(32) (i.e. 31+33+32/3)
23555212	Chips	Potato Lays Chips 50 gms	7/1/2015	22		(32) (i.e. 31+33+32/3)
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		(32.75) (i.e. 34+31+32+34/4)
35442011	Chips	Chilly Lays Chips 50 gms	5/1/2015		32
35442011	Chips	Chilly Lays Chips 50 gms	6/1/2015		34

How to do this?

Thanks,

Ganesh K

[Steelers_In_DC · Accepted solution]

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;

[Ksharp]

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;