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

Hi,

 

I have product and sales in my input data, I want to create the result column as "Wanted_sales_sum". That column is basically sum of sales field within each product group.

 

 

Product                  Sales            Wanted_sales_sum

Amer                        100                   100

Amer                        100                   200

Amer                          50                   250 

Amer                        100                   350

Amer                         50                    400

Pac                          90                      90

Pac                         110                     200

Pac                          70                       270

ctw                          50                       50

ctw                           80                      130

ctw                           70                       200

ctw                           100                     300

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

To get the running mean instead of the sum:

 

data want;
set have; by Product notsorted;
if first.Product then do;
    Sum = 0;
    n = 0;
    end;
Sum + Sales;
n + 1;
Wanted_Sales_Mean = Sum / n;
drop Sum n;
run;
PG

View solution in original post

7 REPLIES 7
Jagadishkatam
Amethyst | Level 16
proc sort data=have;
by product;
run;

data want;
set have;
by product;
retain Wanted_sales_sum;
if first.product then Wanted_sales_sum=sales;
else Wanted_sales_sum+sales;
run;
Thanks,
Jag
helloSAS
Obsidian | Level 7

Thanks!

 

How do I do the same for getting average?

 

Product                  Sales            Wanted_sales_sum

Amer                        100                   100

Amer                        100                   100 (=200/2)

Amer                          50                   83.33 (=250/3)

Amer                        100                   87.5 (=350/4)

Amer                         50                    80 (=400/5)

Pac                          90                      90

Pac                         110                     100 (=200/2)

Pac                          70                       90 (=270/3)

ctw                          50                       50 

ctw                           80                      65 (=130/2)

ctw                           70                       66.66 (=200/3)

ctw                           100                     75 (=300/4)

helloSAS
Obsidian | Level 7
how do I get average instead of sum in the same situation?
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
PGStats
Opal | Level 21

To get the running mean instead of the sum:

 

data want;
set have; by Product notsorted;
if first.Product then do;
    Sum = 0;
    n = 0;
    end;
Sum + Sales;
n + 1;
Wanted_Sales_Mean = Sum / n;
drop Sum n;
run;
PG
PGStats
Opal | Level 21

Might be better to keep original Product order with notsorted option. Retain is not required with sum statement variables (they are retained by default). 

 

data want;
set have; by Product notsorted;
if first.Product then Wanted_Sales_Sum = 0;
Wanted_Sales_Sum + Sales;
run;
PG
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

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