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
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;
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;
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;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: