BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Filipvdr
Pyrite | Level 9

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

1 ACCEPTED SOLUTION

Accepted Solutions
Haikuo
Onyx | Level 15

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;

View solution in original post

5 REPLIES 5
Gerd47
Calcite | Level 5

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
Pyrite | Level 9

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
Calcite | Level 5

Could you mark iot as answered please ?

Thanks

Gerd

Haikuo
Onyx | Level 15

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
Pyrite | Level 9

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 966 views
  • 3 likes
  • 3 in conversation