Nested query of sum for grouped average

Accepted Solution Solved
Reply
Contributor BSL
Contributor
Posts: 25
Accepted Solution

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
Trusted Advisor
Posts: 1,228

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;

View solution in original post


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
Trusted Advisor
Posts: 1,228

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 BSL
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: 18,997

Re: Nested query of sum for grouped average

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 342 views
  • 0 likes
  • 4 in conversation