topic Re: how derive average and insert into a dataset in SAS Programming

how derive average and insert into a dataset

thanikondharish — Thu, 26 Jul 2018 04:57:20 GMT

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

Re: how derive average and insert into a dataset

novinosrin — Thu, 26 Jul 2018 05:04:12 GMT

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?

Re: how derive average and insert into a dataset

thanikondharish — Thu, 26 Jul 2018 05:26:42 GMT

first id group of third month sale is missing so i want to fill up third
month as average of 1 and 2 month and that record insert into a same dataset

Re: how derive average and insert into a dataset

novinosrin — Thu, 26 Jul 2018 05:42:45 GMT

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;

Re: how derive average and insert into a dataset

s_manoj — Thu, 26 Jul 2018 06:24:13 GMT

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;

Re: how derive average and insert into a dataset

thanikondharish — Thu, 26 Jul 2018 06:25:42 GMT

You mentioned month=2 in arrays but in some cases different groups have
different months then what will we do?

Re: how derive average and insert into a dataset

novinosrin — Thu, 26 Jul 2018 06:33:07 GMT

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

Re: how derive average and insert into a dataset

s_manoj — Thu, 26 Jul 2018 06:33:51 GMT

@thanikondharish
As per your wordings, we understand that you need an average for the third month, so month =2 has been mentioned. Please specify your requirement clearly with sample output

Re: how derive average and insert into a dataset

thanikondharish — Thu, 26 Jul 2018 06:40:42 GMT

data s ;
input id $ month sale ;
cards ;
111 1 450
111 2 870
111 3 485
112 1 485
112 2 478
112 3 784
112 4 784
113 1 789
113 2 789
;

i want every 3rd and 4th month sale but in this data only one group has 4th
month so remaining groups haven't 4th month sales . So first of all we get
average of before months and that value assign to 4th month

Re: how derive average and insert into a dataset

thanikondharish — Thu, 26 Jul 2018 06:46:42 GMT

data s ;
input id $ month sale ;
cards ;
111 1 450
111 2 870
111 3 485
112 1 485
112 2 478
112 3 784
112 4 784
113 1 789
113 2 789
;

i want 4th month sale in every group but in some groups haven't 4th month
sale.So first of all we need to get the average of 1th,2nd,3rd months and
that value assign to 4th month and one more question first off all how to
find which months are missed