I have the next data
BALANCE_AMOUNT BUCKET_NUMBER CREDIT_NUMBER NEW_TOB m_ever30 mob_e30 ever30
6383 12 17418 0 6383 0 1
6383 12 17418 1 6383 0 1
6383 12 17418 2 6383 0 1
6383 12 17418 3 6383 0 1
6383 12 17418 4 6383 0 1
6383 12 17418 5 6383 0 1
6383 12 17418 6 6383 0 1
6383 12 17418 7 6383 0 1
6383 12 17418 8 6383 0 1
6383 12 17418 9 6383 0 1
6383 12 17418 10 6383 0 1
6383 12 17418 11 6383 0 1
6383 12 17418 12 6383 0 1
where NEW_TOB variable represents the month. What I need is to obtian the average BALANCE_AMOUNT at 3 months, 6 months and 12 months.
I've been trying a do loop with arrays but I can't figure it out pretty well.
Please help me.
What do you mean by average balance at 3/6/12?
Would that simply be taking all records with NEW_TOB= 3, 6, 12 and averaging the values in the Balance Amount column?
@fluud_nozzle wrote:
I have the next data
BALANCE_AMOUNT BUCKET_NUMBER CREDIT_NUMBER NEW_TOB m_ever30 mob_e30 ever30
6383 12 17418 0 6383 0 1
6383 12 17418 1 6383 0 1
6383 12 17418 2 6383 0 1
6383 12 17418 3 6383 0 1
6383 12 17418 4 6383 0 1
6383 12 17418 5 6383 0 1
6383 12 17418 6 6383 0 1
6383 12 17418 7 6383 0 1
6383 12 17418 8 6383 0 1
6383 12 17418 9 6383 0 1
6383 12 17418 10 6383 0 1
6383 12 17418 11 6383 0 1
6383 12 17418 12 6383 0 1
where NEW_TOB variable represents the month. What I need is to obtian the average BALANCE_AMOUNT at 3 months, 6 months and 12 months.
I've been trying a do loop with arrays but I can't figure it out pretty well.
Please help me.
Try a PROC MEANS to start I suppose, this would filter the data taking only records that are in month 3, 6 & 9 and calculate the average of the variable Balance Amount. It would display and also create a data set called WANT with the summary statistics.
Replace Month with your variable name.
proc means data=have;
where month in (3, 6, 9); *replace month with your month variable;
var Balance_Amount;
ods output summary=want;
run;
@fluud_nozzle wrote:
I need the mean BALANCE_AMOUNT at 3 months, 6 months and 12 months.
The database change in the BALANCE_AMOUNT, this is only a part for one client.
It doesn't work that way...
No, PROC MEANS is not correct in this case. Unfortunately I don't have time now to draft a full answer.
You can search Multilabel formats which is what I would use in this case in conjunction with proc means.
You could also look at PROC EXPAND for moving time series if you have SAS/ETS licensed.
Good Luck.
@fluud_nozzle wrote:
It doesn't work that way...
BALANCE_AMOUNT BUCKET_NUMBER CREDIT_NUMBER NEW_TOB m_ever30 mob_e30 ever30
6383 12 17418 0 6383 0 1
6384 12 17418 1 6383 0 1
6385 12 17418 2 6383 0 1
6386 12 17418 3 6383 0 1
6387 12 17418 4 6383 0 1
6388 12 17418 5 6383 0 1
6389 12 17418 6 6383 0 1
6390 12 17418 7 6383 0 1
6391 12 17418 8 6383 0 1
6392 12 17418 9 6383 0 1
6393 12 17418 10 6383 0 1
6390 12 17418 11 6383 0 1
6700 12 17418 12 6383 0 1
I need for 3 months
(6383 + 6384 + 6385)/3
for 6 months
(6383 + 6384 + 6385 + 6386 + 6387+ 6388)/6
and so on.
Since you still have not displayed what a resulting data set would look like, I'm only guessing what you want. It appears that you want year-to-date cumulative means at 3,6,and 12 months. But at month 3, you actually want the mean over the 3 PRECEDING months (0, 1, 2). If so, this gets the number, but may or may not be the desired data format:
data want;
set have;
by id;
cumulative_bal+balance;
if first.id then cumulative_bal=balance;
mean3=ifn(month=3,lag(cumulative_bal)/3,.);
mean6=ifn(month=6,lag(cumulative_bal)/6,.);
mean12=ifn(month=12,lag(cumulative_bal)/12,.);
if month in (3,6,12);
run;
And what does 0 for a "month" mean? Does this "month" get included in any of the "means" calculations?
Now go back to your example data and add in some hand calculations to show us 1) what your expected variable name for the results are and 2) some of the hand calculated variables.
Your explanation is insufficient as we cannot tell if each month you want the mean of 3 different months or ONLY when month = 3 do you want the mean for months 0,1 and 2,
Also you say "this is only a part for one client". How do we know what a client is? None of your variables says "client".
This works as expected. It's also less code and probably faster and if you need other summary stats you can add it in as well. This was the sum you can do the mean by changing the word SUM to MEAN.
if you're grouping by another variable, ie Client, add Client to the BY statement as well, before the MONTH most likely.
@fluud_nozzle wrote:
Yes, 0 is the first month (e.g. January) and 12 y the 13rd month.
Here's what i've beeing trying
DATA HIPO_PERFO2M;
SET HIPO_PERFO2 (OBS=100);
ARRAY BALANCE{0:12} ;
DO I=0 TO 12;
BALANCE(I)=BALANCE_AMOUNT;
IF I>=1 THEN DO;
BALANCE(I)=BALANCE(I-1);
END;
END;
/*DO I =0 TO 12 ;
BALANCEN=BALANCE(I) ;
END; */
/*
IF NEW_TOB<=2
THEN DO I=0 TO 2;
PROM3[I]= MEAN(BALANCE_AMOUNT);
END;
ARRAY PROM6{0:5};
IF NEW_TOB<=5
THEN DO I=0 TO 5;
PROM6[I]=MEAN(BALANCE_AMOUNT);
END;*/
RUN;
That is an excellent responde, defenitly I'll try it, but, what I need is that information be on the dataset not like a proc means.
What I want to say is that I need three variables e.g. BA@3M=Balance Amount at 3 Months, BA@6M=Balance Amount at 6 Months and BA@9M=Balance Amount at 9 Months, and those variables appears in the data table, not like proc means, that's what i was trying an array with a do loop.
@fluud_nozzle wrote:
That is an excellent responde, defenitly I'll try it, but, what I need is that information be on the dataset not like a proc means.
What I want to say is that I need three variables e.g. BA@3M=Balance Amount at 3 Months, BA@6M=Balance Amount at 6 Months and BA@9M=Balance Amount at 9 Months, and those variables appears in the data table, not like proc means, that's what i was trying an array with a do loop.
Try it again and check the log and output, it generates a data set.
@fluud_nozzle wrote:
That is an excellent responde, defenitly I'll try it, but, what I need is that information be on the dataset not like a proc means.
What I want to say is that I need three variables e.g. BA@3M=Balance Amount at 3 Months, BA@6M=Balance Amount at 6 Months and BA@9M=Balance Amount at 9 Months, and those variables appears in the data table, not like proc means, that's what i was trying an array with a do loop.
And yet you still have not shown what the result is supposed to look like as a data set.
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.