Solved
Contributor
Posts: 25

# Nested query of sum for grouped average

Hi All,

I am trying to get the sum of averaged and grouped data for below mention sample data co could anybody please help to to correct the code. I am trying

to achieve the same result with below mentioned code:

proc sql;

select date, shift, sum(aiprn) as SIPRN, qty, sales

from (select date, shift, avg(iprn) as AIPRN, qty, sales from test group by date, shift)

group by date, shift;

quit;

HAVE**********************************

DATE      SHIFT   IPRN  QTY SALE

05Jan2014 Day     12    1   55

05Jan2014 Day     11    3   44

05Jan2014 Day     10    4   33

05Jan2014 Night   07    1   54

05Jan2014 Night   07    6   43

06Jan2014 Day     08    6   23

06Jan2014 Day     12    5   12

06Jan2014 Night   07    8   41

06Jan2014 Night   09    4   15

06Jan2014 Night   11    1   38

WANT*********************************

DATE      SHIFT   IPRN  QTY SALE

05Jan2014 Day     18    1   55

05Jan2014 Day     18    3   44

05Jan2014 Day     18    4   33

05Jan2014 Night   18    1   54

05Jan2014 Night   18    6   43

06Jan2014 Day     19    6   23

06Jan2014 Day     19    5   12

06Jan2014 Night   19    8   41

06Jan2014 Night   19    4   15

06Jan2014 Night   19    1   38

Regards

Rahul

Accepted Solutions
Solution
‎11-12-2014 09:31 AM
Posts: 1,270

## Re: Nested query of sum for grouped average

proc sql;

select date, shift, sum(distinct aiprn) as SIPRN, qty, sales

from (select date, shift, avg(iprn) as AIPRN, qty, sales from have group by date, shift)

group by date;

quit;

All Replies
Super Contributor
Posts: 578

## Re: Nested query of sum for grouped average

This might get you what you want based on your description...but it doesn't match the dataset you have as WANT.

proc sql;

create table want as

select

t1.*,

t2.Avg_IRPN,

t2.SUM_IRPN

from

have t1

inner join (select date, Avg(irpn) as Avg_IRPN, Sum(IRPN) as Sum_IRPN from have group by date) t2

on t1.date=t2.date

;

quit;

Solution
‎11-12-2014 09:31 AM
Posts: 1,270

## Re: Nested query of sum for grouped average

proc sql;

select date, shift, sum(distinct aiprn) as SIPRN, qty, sales

from (select date, shift, avg(iprn) as AIPRN, qty, sales from have group by date, shift)

group by date;

quit;

Contributor
Posts: 25

## Re: Nested query of sum for grouped average

There is slightly changes in the data...so can it be achieved in SAS.

I am posting a new Question now.

Super User
Posts: 23,683

## Re: Nested query of sum for grouped average

🔒 This topic is solved and locked.