Programming Logic

[Filipvdr]

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

[Gerd47]

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);

[Filipvdr]

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

[Gerd47]

Could you mark iot as answered please ?

Thanks

Gerd

[Haikuo · Accepted solution]

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;

[Filipvdr]

thanks Hai.kuo , your reply is comes close to what I really needed