topic Re: MEAN FOR SOME VARIABLES FOR 3, 6 AND 12 MONTHS in SAS Programming

MEAN FOR SOME VARIABLES FOR 3, 6 AND 12 MONTHS

fluud_nozzle — Thu, 28 Jun 2018 21:40:29 GMT

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.

Re: MEAN FOR SOME VARIABLES FOR 3, 6 AND 12 MONTHS

Reeza — Thu, 28 Jun 2018 22:21:41 GMT

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.

Re: MEAN FOR SOME VARIABLES FOR 3, 6 AND 12 MONTHS

fluud_nozzle — Thu, 28 Jun 2018 22:27:41 GMT

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.

Re: MEAN FOR SOME VARIABLES FOR 3, 6 AND 12 MONTHS

Reeza — Thu, 28 Jun 2018 22:31:21 GMT

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.

Re: MEAN FOR SOME VARIABLES FOR 3, 6 AND 12 MONTHS

fluud_nozzle — Thu, 28 Jun 2018 22:32:13 GMT

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.

Re: MEAN FOR SOME VARIABLES FOR 3, 6 AND 12 MONTHS

fluud_nozzle — Thu, 28 Jun 2018 22:52:21 GMT

It doesn't work that way...

Re: MEAN FOR SOME VARIABLES FOR 3, 6 AND 12 MONTHS

Reeza — Thu, 28 Jun 2018 22:55:46 GMT

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...

Re: MEAN FOR SOME VARIABLES FOR 3, 6 AND 12 MONTHS

ballardw — Thu, 28 Jun 2018 22:57:16 GMT

And what does 0 for a "month" mean? Does this "month" get included in any of the "means" calculations?

Re: MEAN FOR SOME VARIABLES FOR 3, 6 AND 12 MONTHS

fluud_nozzle — Thu, 28 Jun 2018 23:01:25 GMT

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;

Re: MEAN FOR SOME VARIABLES FOR 3, 6 AND 12 MONTHS

ballardw — Thu, 28 Jun 2018 23:26:45 GMT

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".

Re: MEAN FOR SOME VARIABLES FOR 3, 6 AND 12 MONTHS

Reeza — Fri, 29 Jun 2018 01:43:33 GMT

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;

Re: MEAN FOR SOME VARIABLES FOR 3, 6 AND 12 MONTHS

fluud_nozzle — Fri, 29 Jun 2018 14:07:51 GMT

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.

Re: MEAN FOR SOME VARIABLES FOR 3, 6 AND 12 MONTHS

Reeza — Fri, 29 Jun 2018 15:03:36 GMT

@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.

Re: MEAN FOR SOME VARIABLES FOR 3, 6 AND 12 MONTHS

ballardw — Fri, 29 Jun 2018 15:24:01 GMT

@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.

Re: MEAN FOR SOME VARIABLES FOR 3, 6 AND 12 MONTHS

mkeintz — Fri, 29 Jun 2018 22:41:07 GMT

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;