Hello,
I have a dateset which looks like this:
Month Value A Value B
MAR2011 10% 20
APR2011 11% 20
JUN2011 10% 27
JUL2011 10% 22
AUG2011 15% 20
SEPT2011 12% 25
OKT2011 10% 23
NOV2011 10% 21
The macro variable disp_bin is the number of months that I have to summarize on. For example if the disp_bin is 3 then
SEPT,OKT en NOV should be one row with the average of value A and value B as new value A and B
AUG JUL en JUN should be one row with the average of value A and value B as new value A and B
MAR and APR should be one row (so actually i have to start at the last month) with the average of value A and value B as new value A and B
I hope it is clear like this,
Thanks
Or: 1XDOW will do the trick, no need to involve macros.
data have;
input month:$ va :percent5. vb;
cards;
MAR2011 10% 20
APR2011 11% 20
JUN2011 10% 27
JUL2011 10% 22
AUG2011 15% 20
SEPT2011 12% 25
OKT2011 10% 23
NOV2011 10% 21
;
data want (keep=new:);
length New_month $30;
format new_va percent9.2;
do i=1 to ifn(_n_=1,mod(nobs,3),3);
set have nobs=nobs;
if i=1 then s_mon=month;
tva+va;
tvb+vb;
end;
new_va=tva/(i-1);
new_vb=tvb/(i-1);
New_month=catx('-',s_mon,month);
output;
call missing(tva,tvb);
run;
proc print;run;
Something like that ?
%macro test(disp_bin=);
data test1;
length month $ 200;
set test;
n=_N_;
run;
proc sort data=test1; by descending n; run;
data test1;
set test1;
n1+1;
if (n1>&disp_bin) then do;
n1=1;
group+1;
end;
run;
proc sql;
create table test2 as
select group, mean(valuea) as meana, mean(valueb) as meanb
from test1
group by group
order by group desc;
quit;
%mend;
%test(disp_bin=3);
yes, that is what i needed more or less.
I forgot to say that I need a name as well for the group.
SEPT,OKT and NOV should become something like SEPT-NOV
I will try to figure it out, thanks already
Could you mark iot as answered please ?
Thanks
Gerd
Or: 1XDOW will do the trick, no need to involve macros.
data have;
input month:$ va :percent5. vb;
cards;
MAR2011 10% 20
APR2011 11% 20
JUN2011 10% 27
JUL2011 10% 22
AUG2011 15% 20
SEPT2011 12% 25
OKT2011 10% 23
NOV2011 10% 21
;
data want (keep=new:);
length New_month $30;
format new_va percent9.2;
do i=1 to ifn(_n_=1,mod(nobs,3),3);
set have nobs=nobs;
if i=1 then s_mon=month;
tva+va;
tvb+vb;
end;
new_va=tva/(i-1);
new_vb=tvb/(i-1);
New_month=catx('-',s_mon,month);
output;
call missing(tva,tvb);
run;
proc print;run;
thanks Hai.kuo , your reply is comes close to what I really needed
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.