turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- Calculating average debt

Topic Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-10-2017 03:52 PM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to gandikk

02-10-2017 06:32 PM

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

All Replies

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to gandikk

02-10-2017 04:09 PM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to art297

02-10-2017 04:21 PM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to gandikk

02-10-2017 04:57 PM

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;

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ballardw

02-10-2017 05:12 PM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to gandikk

02-10-2017 05:11 PM

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

Art, CEO, AnalystFinder.com

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to art297

02-10-2017 05:20 PM

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

SAS@EMMAUS

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to gandikk

02-10-2017 05:20 PM - edited 02-10-2017 05:37 PM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to art297

02-10-2017 06:14 PM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to gandikk

02-10-2017 06:23 PM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to gandikk

02-10-2017 06:32 PM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to art297

02-13-2017 10:05 AM

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

SAS@EMMAUS