DATA Step, Macro, Functions and more

MEAN FOR SOME VARIABLES FOR 3, 6 AND 12 MONTHS

Reply
Occasional Contributor
Posts: 6

MEAN FOR SOME VARIABLES FOR 3, 6 AND 12 MONTHS

[ Edited ]

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.

Super User
Posts: 23,778

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

Posted in reply to fluud_nozzle

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.


 

Occasional Contributor
Posts: 6

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

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.
Super User
Posts: 23,778

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

Posted in reply to fluud_nozzle

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.



Occasional Contributor
Posts: 6

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

It doesn't work that way...

Super User
Posts: 23,778

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

Posted in reply to fluud_nozzle

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


 

Occasional Contributor
Posts: 6

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

Posted in reply to fluud_nozzle

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.

 

Trusted Advisor
Posts: 1,346

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

Posted in reply to fluud_nozzle

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;
Super User
Posts: 13,583

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

Posted in reply to fluud_nozzle

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

Occasional Contributor
Posts: 6

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

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;

Super User
Posts: 13,583

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

Posted in reply to fluud_nozzle

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

 

Super User
Posts: 23,778

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

[ Edited ]
Posted in reply to fluud_nozzle

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. 

 

Screen Shot 2018-06-28 at 7.38.07 PM.png


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


 

Occasional Contributor
Posts: 6

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

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.

 

 

Super User
Posts: 23,778

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

Posted in reply to fluud_nozzle

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

Super User
Posts: 13,583

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

Posted in reply to fluud_nozzle

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

Ask a Question
Discussion stats
  • 14 replies
  • 146 views
  • 0 likes
  • 4 in conversation