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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.