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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.