BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
gandikk
Obsidian | Level 7

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
1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

11 REPLIES 11
art297
Opal | Level 21

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

 

gandikk
Obsidian | Level 7

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
ballardw
Super User

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;

gandikk
Obsidian | Level 7
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
art297
Opal | Level 21

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

 

Art, CEO, AnalystFinder.com

 

gandikk
Obsidian | Level 7
art297, I need the result in a dataset. Thanks!!
SAS@EMMAUS
art297
Opal | Level 21
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

 

gandikk
Obsidian | Level 7

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
Reeza
Super User

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.

art297
Opal | Level 21

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

gandikk
Obsidian | Level 7

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

SAS@EMMAUS

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