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
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
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
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 |
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;
Do you need a printout of those values or a new data file?
Art, CEO, AnalystFinder.com
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
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)
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.
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
Thank you art297!! I got the desired output based on your solution.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.