Nested query of sum for grouped average

[BSL]

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

[DBailey]

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;

[stat_sas · Accepted solution]

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;

[BSL]

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

I am posting a new Question now.