BookmarkSubscribeRSS Feed
Bill
Quartz | Level 8
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.)
;
4 REPLIES 4
abdullala
Calcite | Level 5
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.
abdullala
Calcite | Level 5
sorry, the quarter variable should be put prior to the date variable for grouping.
Bill
Quartz | Level 8
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
;
Bill
Quartz | Level 8
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
;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 8592 views
  • 0 likes
  • 2 in conversation