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
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;
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;
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;
There is slightly changes in the data...so can it be achieved in SAS.
I am posting a new Question now.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.