data fi ;
input id $ month sale ;
cards ;
111 1 150
111 2 250
112 1 200
112 2 300
112 3 250
;
111 1 150
111 2 250
111 3 200
112 1 200
112 2 300
112 3 250
;
i want id 111 third month sale as average of first and second month like this 111 3 200
and insert into a dataset
Hi @thanikondharish do you only have sets of 2 or 3 for each id in your dataset? and is it consistent you want the 3rd month where not available?
data fi ;
input id $ month sale ;
cards ;
111 1 150
111 2 250
112 1 200
112 2 300
112 3 250
;
data want;
array t(100) _temporary_;
call missing(of t(*));
do _n_=1 by 1 until(last.id);
set fi;
by id month;
t(_n_)=sale;
output;
if last.id and month=2 then do;
month=3;
sale=mean(of t(*));
output;
end;
end;
run;
Please provide a better and complete sample.
Thats the reason i asked earlier "Hi @thanikondharish do you only have sets of 2 or 3 for each id in your dataset? and is it consistent you want the 3rd month where not available?"
You didn't clarify
I hope you understood what im asking
data want(drop = n sumsale);
set fi;
by id;
if first.id then do;
n = 0;
sumsale = 0;
end;
n +1;
sumsale + sale;
output;
if last.id & month = 2 then do;
month=3;
sale = sumsale/n;
output;
end;
run;
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.