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

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
  • 2 replies
  • 1632 views
  • 0 likes
  • 2 in conversation