topic None unique GROUP BY items in output table in SAS Procedures

None unique GROUP BY items in output table

niemannk — Thu, 16 Jun 2011 07:58:06 GMT

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;

Re: None unique GROUP BY items in output table

DBailey — Thu, 16 Jun 2011 12:03:27 GMT

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.

Re: None unique GROUP BY items in output table

niemannk — Thu, 16 Jun 2011 12:24:55 GMT

WOW! This works! I think they should include that topic in their seminar " SAS Dinosaurier" (they offer something called like that in Germany).