topic sql group by wth date format in SAS Procedures

sql group by wth date format

Bill — Tue, 19 Jan 2010 20:58:04 GMT

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.)
;

Re: sql group by wth date format

abdullala — Tue, 19 Jan 2010 21:25:24 GMT

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.

Re: sql group by wth date format

abdullala — Tue, 19 Jan 2010 21:28:56 GMT

sorry, the quarter variable should be put prior to the date variable for grouping.

Re: sql group by wth date format

Bill — Tue, 19 Jan 2010 21:47:26 GMT

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
;

Re: sql group by wth date format

Bill — Wed, 20 Jan 2010 14:51:23 GMT

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
;