turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- SAS Procedures
- /
- sql group by wth date format

Topic Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-19-2010 03:58 PM

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

;

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

;

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Bill

01-19-2010 04:25 PM

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.

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

;

see if this works.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Bill

01-19-2010 04:28 PM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to abdullala

01-19-2010 04:47 PM

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

;

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

;

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Bill

01-20-2010 09:51 AM

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

;

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

;