Help using Base SAS procedures

None unique GROUP BY items in output table

Reply
Contributor
Posts: 29

None unique GROUP BY items in output table

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;
Super Contributor
Posts: 578

Re: None unique GROUP BY items in output table

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.
Contributor
Posts: 29

Re: None unique GROUP BY items in output table

WOW! This works! I think they should include that topic in their seminar " SAS Dinosaurier" (they offer something called like that in Germany).
Ask a Question
Discussion stats
  • 2 replies
  • 746 views
  • 0 likes
  • 2 in conversation