topic Nested query of sum for grouped average in SAS Programming

Nested query of sum for grouped average

BSL — Wed, 12 Nov 2014 13:03:26 GMT

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

Re: Nested query of sum for grouped average

DBailey — Wed, 12 Nov 2014 14:19:09 GMT

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;

Re: Nested query of sum for grouped average

stat_sas — Wed, 12 Nov 2014 14:31:56 GMT

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;

Re: Nested query of sum for grouped average

Reeza — Wed, 12 Nov 2014 14:55:19 GMT

Re: Nested query of sum for grouped average

BSL — Thu, 13 Nov 2014 05:17:26 GMT

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

I am posting a new Question now.