BookmarkSubscribeRSS Feed
niemannk
Calcite | Level 5
Hi,
I don't understand why the following does not result in unique GROUP BY items:

proc sql;
create table sum1 as
select code
, month(date_) as month
, year(date_) as year
, sum(col_X) as sum_
from table1
group by code, month(date_),year(date_);
quit;

Table sum1 has just as many observation as the input table1 (assume table1 has daily observations). Sum1 is not unique by the GROUP By items. This is an unexpected results to me. Any other SQL implementation (for exp MS SQL) returns unique group by items.

This code results in unique GROUP BY items:

data table2;
set table1;
month=month(date_);
year=year(date_);
day=day(date_);
run;

proc sql;
create table sum2 as
select code
, month
, year
, sum(col_X) as sum_
from table1
group by code, month,year;
quit;
2 REPLIES 2
DBailey
Lapis Lazuli | Level 10
I have seen this before. I think I corrected it by using:


proc sql;
create table sum1 as
select code
, month(date_) as month
, year(date_) as year
, sum(col_X) as sum_
from table1
group by code,calculated month, calculated year;
quit;

I believe it is a true statement that SAS has not implemented ANSI SQL standards.
niemannk
Calcite | Level 5
WOW! This works! I think they should include that topic in their seminar " SAS Dinosaurier" (they offer something called like that in Germany).

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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
  • 2 replies
  • 1772 views
  • 0 likes
  • 2 in conversation