topic Re: Calculating average debt in SAS Programming

Calculating average debt

gandikk — Fri, 10 Feb 2017 20:52:15 GMT

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

Re: Calculating average debt

art297 — Fri, 10 Feb 2017 21:09:53 GMT

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

Re: Calculating average debt

gandikk — Fri, 10 Feb 2017 21:21:40 GMT

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

Re: Calculating average debt

ballardw — Fri, 10 Feb 2017 21:57:06 GMT

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;

Re: Calculating average debt

art297 — Fri, 10 Feb 2017 22:11:25 GMT

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

Art, CEO, AnalystFinder.com

Re: Calculating average debt

gandikk — Fri, 10 Feb 2017 22:12:22 GMT

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.

Re: Calculating average debt

gandikk — Fri, 10 Feb 2017 22:20:01 GMT

art297, I need the result in a dataset. Thanks!!

Re: Calculating average debt

art297 — Fri, 10 Feb 2017 22:37:59 GMT

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

Re: Calculating average debt

gandikk — Fri, 10 Feb 2017 23:14:20 GMT

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)

Re: Calculating average debt

Reeza — Fri, 10 Feb 2017 23:23:28 GMT

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.

Re: Calculating average debt

art297 — Fri, 10 Feb 2017 23:32:18 GMT

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

Re: Calculating average debt

gandikk — Mon, 13 Feb 2017 15:05:31 GMT

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