Help using Base SAS procedures

sql group by wth date format

Reply
Super Contributor
Posts: 291

sql group by wth date format

The code I'm trying to use follows. Unfortunately, the output data is not grouped by Quarter. How do I accomplish Quarterly grouping in the SQL setting? I'm trying to avoid using proc summary plus a data step.

Thanks!

proc sql;
create table weights as

select put(date,yyq6.),
sum(slabwt) as EntryWt,
sum(hrcoilwt) as ExitWt,
(sum(slabwt)/sum(hrcoilwt))*1000 as Yield

from HMQandY

where EndTest='N'

group by put(date,yyq6.)
;
Contributor
Posts: 74

Re: sql group by wth date format

if you want to sum the data by quarter (as you said the data is not grouped by quarter), you have to have the variable of quarter. you may use qtr(date) to get it and add it to your grouping variables.


proc sql;
create table weights as

select put(date,yyq6.),

qtr(date) as quarter,

sum(slabwt) as EntryWt,
sum(hrcoilwt) as ExitWt,
(sum(slabwt)/sum(hrcoilwt))*1000 as Yield

from HMQandY

where EndTest='N'

group by put(date,yyq6.)
, calculated quarter
;

see if this works.
Contributor
Posts: 74

Re: sql group by wth date format

sorry, the quarter variable should be put prior to the date variable for grouping.
Super Contributor
Posts: 291

Re: sql group by wth date format

abdullala;

Thank you for directing me to the solution. Here's what I ended up with.

proc sql;
create table weights as

select put(date,yyq6.) as Qtr,

sum(slabwt) as EntryWt,
sum(hrcoilwt) as ExitWt,
(sum(slabwt)/sum(hrcoilwt))*1000 as Yield

from HMQandY

where EndTest='N'

group by calculated Qtr
;
Super Contributor
Posts: 291

Re: sql group by wth date format

The solution above results in Qtr being a character variable instead of a sas date value. I've reworked the code so that it now groups the data in the same way while retaining the date as a sas date. Code below.

proc sql;
create table HmCoilWt as

select date format=yyq6.,
EndTest,
sum(slabwt) as EntryWt,
sum(hrcoilwt) as ExitWt,
(sum(slabwt)/sum(hrcoilwt))*1000 as ProcessYld

from HMQandY

group by date,
EndTest
;
Ask a Question
Discussion stats
  • 4 replies
  • 2659 views
  • 0 likes
  • 2 in conversation