BookmarkSubscribeRSS Feed
helloSAS
Obsidian | Level 7

Thanks! How do I get the average instead of sum?

 

 

Product                  Sales            Wanted_sales_average

Amer                        100                   100

Amer                        100                   200

Amer                          50                   83.33

Amer                        100                   87.5

Amer                         50                    80

Pac                          90                      90

Pac                         110                     100

Pac                          70                       90

ctw                          50                       50

ctw                           80                      65

ctw                           70                       66.66

ctw                           100                     75

3 REPLIES 3
hbi
Quartz | Level 8 hbi
Quartz | Level 8

This should work nicely. 

 

DATA have;
  length Product $10 Sales 8;
  input Product $ Sales;
datalines;
Amer                        100 
Amer                        100 
Amer                         50 
Amer                        100 
Amer                         50 
Pac                          90 
Pac                         110 
Pac                          70 
ctw                          50 
ctw                          80 
ctw                          70 
ctw                         100 
;
RUN;


/* this assumes that your data is already sorted by "Product" */ DATA want; SET have; BY Product; FORMAT Wanted_Sales_Average 10.2; IF first.Product THEN DO; i = 1; Running_Sales_Sum = Sales; END; ELSE DO; i + 1; Running_Sales_Sum + Sales; END; Wanted_Sales_Average = Running_Sales_Sum/i; /* DROP i Running_Sales_Sum; */ RUN;

  sas_sum_vertically.gif

hbi
Quartz | Level 8 hbi
Quartz | Level 8

Here is the PROC SQL version that accomplishes the same task. Enjoy Robot Happy

 

DATA have;
  length Product $10 Sales 8;

  /* need counter variable because PROC SQL cannot use "_N_": */
  seq_num = MONOTONIC(); 

  input Product $ Sales;
datalines;
Amer                        100 
Amer                        100 
Amer                         50 
Amer                        100 
Amer                         50 
Pac                          90 
Pac                         110 
Pac                          70 
ctw                          50 
ctw                          80 
ctw                          70 
ctw                         100 
;
RUN;


PROC SQL;
  create table want2 as 
  select Product
       , Sales
       , (select sum(a.Sales)/count(a.Sales)
          from have as a 
          where a.seq_num <= b.seq_num
            and a.Product = b.Product) format=comma10.2 as Wanted_Sales_Average
  from have as b;
QUIT;
MikeZdeb
Rhodochrosite | Level 12

Hi, another data step solution using a DOW ...

 

data have;
input product :$4. sales @@;
datalines;
Amer 100 Amer 100 Amer 50 Amer 100
Amer 50 Pac 90 Pac 110 Pac 70
ctw 50 ctw 80 ctw 70 ctw 100
;

 

data want;
tot = 0;
do j=1 by 1 until (last.product);
   set have;
   by product;
   tot+sales;
   avg = tot /j;
   output;
end;
drop j tot;
run;

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
  • 3 replies
  • 735 views
  • 0 likes
  • 3 in conversation