DATA Step, Macro, Functions and more

Calculating average debt

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 19
Accepted Solution

Calculating average debt

Hi,

 

I have 6 datasets and each dataset has a customer ID, total anual revenue and 6 past due amounts. I need to calculate average past due for all the 6 pass due fields based on the formula--

AVERAGE PAST DUE 1 = (sum of six months past due1 from all 6 datasets / sum of six months of total A/R from all 6 datasets)

AVERAGE PAST DUE 2 = (sum of six months past due2 from all 6 datasets / sum of six months of total A/R from all 6 datasets)

 

.

.

AVERAGE PAST DUE 6 = (sum of six months past due6 from all 6 datasets / sum of six months of total A/R from all 6 datasets)

 

data look like below from all 6 datasets:

 

Cust ID Total Annual Revenue past due1 past due2 past due3 past due4 past due5 past due6
AB123 3456.78 0.00 4567.78 0.00 0.00 0.00 0.00
BC345 1234.55 -345.78 -966.05 3736.51 464.19 8467.97 -24225.95
GH456 685.88 437.00 248.88 0.00 0.00 0.00 0.00
TR678 54001.54 0.00 0.00 0.00 -35.14 0.00 0.00
RE789 6241.89 -49.11 0.00 0.00 0.00 0.00 0.00

 

Could you let me know how to get the desired result?

 

Thanks in advance

SAS@EMMAUS

Accepted Solutions
Solution
‎02-13-2017 10:05 AM
PROC Star
Posts: 7,356

Re: Calculating average debt

Then I would use proc sql. e.g.,

 

data combined;
  set dataset1 dataset2 dataset3 dataset4 dataset5 dataset6;
run;

proc sql noprint;
  create table want as
    select sum(AR)/sum(pastdue1) as pastdue1,
           sum(AR)/sum(pastdue2) as pastdue2,
           sum(AR)/sum(pastdue3) as pastdue3,
           sum(AR)/sum(pastdue4) as pastdue4,
           sum(AR)/sum(pastdue5) as pastdue5,
           sum(AR)/sum(pastdue6) as pastdue6
      from combined
        group by cust
  ;
quit;

HTH,

Art, CEO, AnalystFinder.com

View solution in original post


All Replies
PROC Star
Posts: 7,356

Re: Calculating average debt

Do you want average past_due amounts for each customer or just the six averages of all of the data?

 

Regardless, the first step would be to combine the six files into one file. Then, using that file, you could do either of the above.

 

HTH,

Art, CEO, AnalystFinder.com

 

Occasional Contributor
Posts: 19

Re: Calculating average debt

I need averages for each customer like below.

 

Cust ID Average Past Due1 Average Past Due2 Average Past Due3 Average Past Due4 Average Past Due5 Average Past Due6
SAS@EMMAUS
Super User
Posts: 10,466

Re: Calculating average debt

I'm not quite sure if you are asking for a PER customer result or not but combining all the data and then.

 

It really helps to provide some data in the form of a data step, and at least variable names.

Using your example data

data combined;
   input cust$ AR pastdue1 pastdue2 pastdue3 pastdue4 pastdue5 pastdue6;
datalines;
AB123 3456.78 0.00 4567.78 0.00 0.00 0.00 0.00 
BC345 1234.55 -345.78 -966.05 3736.51 464.19 8467.97 -24225.95 
GH456 685.88 437.00 248.88 0.00 0.00 0.00 0.00 
TR678 54001.54 0.00 0.00 0.00 -35.14 0.00 0.00 
RE789 6241.89 -49.11 0.00 0.00 0.00 0.00 0.00 
;
run;

proc tabulate data=combined;
   var AR pastdue1 pastdue2 pastdue3 pastdue4 pastdue5 pastdue6;
   tables pastdue1 pastdue2 pastdue3 pastdue4 pastdue5 pastdue6,
          pctsum<ar>;
run;

 

You can make the combined data from your data with

data combined;

    set dataset1 dataset2 <each data set name> ;

run;

Occasional Contributor
Posts: 19

Re: Calculating average debt

Hi ballardw, Thanks for your response!! I am looking for a per customer result. For each customer, we need to populate the average values. Thank you.
SAS@EMMAUS
PROC Star
Posts: 7,356

Re: Calculating average debt

Do you need a printout of those values or a new data file?

 

Art, CEO, AnalystFinder.com

 

Occasional Contributor
Posts: 19

Re: Calculating average debt

art297, I need the result in a dataset. Thanks!!
SAS@EMMAUS
PROC Star
Posts: 7,356

Re: Calculating average debt

[ Edited ]
data combined;
  set dataset1 dataset2 dataset3 dataset4 dataset5 dataset6;
run;

proc summary data=combined;
  var pastdue1-pastdue6;
  class cust;
  output out=want (drop=_:) mean=;
run;

That will produce a file where the first record will contain the overall averages, followed by the averages for each customer. If you don't want/need the overall averages, simply include the nway option in your call to proc summary (i.e.,

proc summary data=combined nway;

 

HTH,

Art, CEO, AnalystFinder.com

 

Occasional Contributor
Posts: 19

Re: Calculating average debt

art297, Thanks for your response!! I have to apply a formula while calculating the average debt. It's not the regular average. Sorry, if I was not clear earlier.

I have to apply the below formula to get the average past due.

 

AVERAGE PAST DUE 1 = (sum of six months past due1 from all 6 datasets / sum of six months of total A/R from all 6 datasets)

AVERAGE PAST DUE 2 = (sum of six months past due2 from all 6 datasets / sum of six months of total A/R from all 6 datasets)

 

.

.

AVERAGE PAST DUE 6 = (sum of six months past due6 from all 6 datasets / sum of six months of total A/R from all 6 datasets)

SAS@EMMAUS
Super User
Posts: 17,750

Re: Calculating average debt

How big are your datasets? 

You either need to combine them first or run proc means on each of them and combine the summaries. 

 

If you're combining the summaries, get the SUM and N statistic that you can use to calculate the average when you do the combination. 

 

You 'll then need to total all the sums and N's from the summaries and calculate the average. 

 

Please explain which of the above steps you need help with if anything is unclear, and include you're code and log if relevant.

Solution
‎02-13-2017 10:05 AM
PROC Star
Posts: 7,356

Re: Calculating average debt

Then I would use proc sql. e.g.,

 

data combined;
  set dataset1 dataset2 dataset3 dataset4 dataset5 dataset6;
run;

proc sql noprint;
  create table want as
    select sum(AR)/sum(pastdue1) as pastdue1,
           sum(AR)/sum(pastdue2) as pastdue2,
           sum(AR)/sum(pastdue3) as pastdue3,
           sum(AR)/sum(pastdue4) as pastdue4,
           sum(AR)/sum(pastdue5) as pastdue5,
           sum(AR)/sum(pastdue6) as pastdue6
      from combined
        group by cust
  ;
quit;

HTH,

Art, CEO, AnalystFinder.com

Occasional Contributor
Posts: 19

Re: Calculating average debt

Thank you art297!! I got the desired output based on your solution.

SAS@EMMAUS
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 11 replies
  • 186 views
  • 0 likes
  • 4 in conversation