BookmarkSubscribeRSS Feed
fluud_nozzle
Calcite | Level 5

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.

14 REPLIES 14
Reeza
Super User

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.


 

fluud_nozzle
Calcite | Level 5
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.
Reeza
Super User

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.



fluud_nozzle
Calcite | Level 5

It doesn't work that way...

Reeza
Super User

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


 

fluud_nozzle
Calcite | Level 5

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.

 

mkeintz
PROC Star

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;
--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
ballardw
Super User

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

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

ballardw
Super User

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

 

Reeza
Super User

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;


 

fluud_nozzle
Calcite | Level 5

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.

 

 

Reeza
Super User

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

ballardw
Super User

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

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
  • 14 replies
  • 1822 views
  • 0 likes
  • 4 in conversation