SQL problem in using dual aggregate function

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 81
Accepted Solution

SQL problem in using dual aggregate function

Hi all,

I want a day vise average and then some day and night on a particular date so can anyone please explain me how can i do so..

data test;

input Date :date9. Shift$ IPRN;

format date date11.;

datalines;

05Jan2014 Day 12

05Jan2014 Day 11

05Jan2014 Day 10

05Jan2014 Night 07

05Jan2014 Night 09

06Jan2014 Day 08

06Jan2014 Day 12

06Jan2014 Night 07

06Jan2014 Night 09

06Jan2014 Night 11

;

run;

Required result:

date               IPRN

05Jan2014     19

06Jan2014      19

Regards

Anand


Accepted Solutions
Solution
‎11-09-2014 07:54 AM
Super User
Posts: 9,682

Re: SQL problem in using dual aggregate function

If I understood what you mean.





data test;
input Date :date9. Shift$ IPRN;
format date date11.;
datalines;
05Jan2014 Day 12
05Jan2014 Day 11
05Jan2014 Day 10
05Jan2014 Night 07
05Jan2014 Night 09
06Jan2014 Day 08
06Jan2014 Day 12
06Jan2014 Night 07
06Jan2014 Night 09
06Jan2014 Night 11
;
run;
 
proc sql;
create table want as
select date,sum(iprn) as iprn 
 from (select date,shift,avg(iprn) as iprn from test group by date,shift)
  group by  date;
quit;

Xia Keshan

View solution in original post


All Replies
Trusted Advisor
Posts: 1,131

Re: SQL problem in using dual aggregate function

Please try , this will give you the day wise average, night wise average and day & night wise average (i believe this is the desired output you are expecting, however the average of that record for the given data would be 9.8)

proc sql;

create table test2 as select date,shift, mean(IPRN) as average from test group by date,shift

union

select date,'day & Night' as shift, mean(IPRN) as average from test group by date;

quit;

Thanks,
Jag

Thanks,
Jag
Frequent Contributor
Posts: 81

Re: SQL problem in using dual aggregate function

Hi Jagdish,

Can we do this using nested select statement as I have to apply this logic in a large query have done before using nested query but am not getting that right now.

Trusted Advisor
Posts: 1,131

Re: SQL problem in using dual aggregate function

i believe we can achieve it , could you please provide the code into which you want to get this code, so i could try to provide a better suggestions. at the moment i am unable to imagine the scenario you are in.

Thanks,
Jag
Frequent Contributor
Posts: 81

Re: SQL problem in using dual aggregate function

I am close to the result using my query as follows:

proc sql;

select date, shift, avg(IPRN)as IPRNo

from (select * from test having sum(iprn))

group by date, shift;

quit;

the original code is mentioned below:

proc sql noprint threads;

  create view TEMP_LASR_VIEW_5737 as

SELECT Production.CmpyCode, Production.Unit, Production.Iddate, Production.IdPlantCode, Production.IdShiftCode, Production.IdPrdCode, - SUM(Production.IdPrdQty) AS ProdQty, SAS_V_PrdMstMI.PrdBBpH,

CASE WHEN (SUM(SAS_V_PrdMstMI.PrdBBpH) IS NULL OR SUM(SAS_V_PrdMstMI.PrdBBpH)=0) THEN . ELSE ((CASE WHEN SUM(Production.IdPrdQty) IS NULL  THEN 0 ELSE -SUM(Production.IdPrdQty) END)/(CASE WHEN SUM(SAS_V_PrdMstMI.PrdBBpH) IS NULL THEN 0 ELSE SUM(SAS_V_PrdMstMI.PrdBBpH) END)) END

AS InHrs, SAS_V_PrdMst.PrdName

FROM libbnpl.SAS_V_FgTData AS Production INNER JOIN libbnpl.SAS_V_PrdMstMI AS SAS_V_PrdMstMI

ON Production.IdPrdCode = SAS_V_PrdMstMI.PrdCode AND Production.IdPlantCode = SAS_V_PrdMstMI.PrdPlantCode

INNER JOIN

libbnpl.SAS_V_PrdMst AS SAS_V_PrdMst

ON SAS_V_PrdMstMI.PrdCode = SAS_V_PrdMst.PrdCode

WHERE     (Production.IdType = 'PR') AND (Production.IdDelTag IS NULL OR

                      Production.IdDelTag <> 'Y')

GROUP BY Production.CmpyCode, Production.Unit, Production.Iddate, Production.IdPlantCode, Production.IdShiftCode, Production.IdPrdCode, SAS_V_PrdMstMI.PrdBBpH, SAS_V_PrdMst.PrdName

HAVING put(datepart(Production.Iddate), date9.) between put('01APR2012'd, date9.) AND put(today(), date9.)

ORDER BY Production.Iddate;

quit;

Solution
‎11-09-2014 07:54 AM
Super User
Posts: 9,682

Re: SQL problem in using dual aggregate function

If I understood what you mean.





data test;
input Date :date9. Shift$ IPRN;
format date date11.;
datalines;
05Jan2014 Day 12
05Jan2014 Day 11
05Jan2014 Day 10
05Jan2014 Night 07
05Jan2014 Night 09
06Jan2014 Day 08
06Jan2014 Day 12
06Jan2014 Night 07
06Jan2014 Night 09
06Jan2014 Night 11
;
run;
 
proc sql;
create table want as
select date,sum(iprn) as iprn 
 from (select date,shift,avg(iprn) as iprn from test group by date,shift)
  group by  date;
quit;

Xia Keshan

Frequent Contributor
Posts: 81

Re: SQL problem in using dual aggregate function

THANK YOU....XIA.

Your code is working fine...that is what I wanted Smiley Happy

but still facing problem on applying this logic in my query so could you please do me one more favor to apply your logic in my query as I tried it many ways but couldn't succeed.

Regards

anand

Super User
Posts: 9,682

Re: SQL problem in using dual aggregate function

That is too mess.  Your query contains too many variables and too many logic .

Post your original data and the output you need.

Xia Keshan

Frequent Contributor
Posts: 81

Re: SQL problem in using dual aggregate function

providing the code is not possible Smiley Sad

Super User
Posts: 9,682

Re: SQL problem in using dual aggregate function

Sorry . Conquer it by yourself.

Super User
Posts: 17,868

Re: SQL problem in using dual aggregate function

You can use two proc means very easily but it looks like you haven't posted your full problem.



data test;

input Date :date9. Shift$ IPRN;

format date date11.;

datalines;

05Jan2014 Day 12

05Jan2014 Day 11

05Jan2014 Day 10

05Jan2014 Night 07

05Jan2014 Night 09

06Jan2014 Day 08

06Jan2014 Day 12

06Jan2014 Night 07

06Jan2014 Night 09

06Jan2014 Night 11

;

run;

proc means data=test sum;

class date shift;

type date*shift;

var IPRN;

output out=want1 mean=tot_iprn;

run;

proc means data=want1 sum;

class date;

var tot_iprn;

run;

☑ This topic is SOLVED.

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

Discussion stats
  • 10 replies
  • 410 views
  • 5 likes
  • 4 in conversation