topic Re: SQL - different results inline x where in ODS and Base Reporting

SQL - different results inline x where

Abud — Tue, 12 Jun 2012 19:57:10 GMT

Hello everybody !!

Can somebody explain why I have two different results ?

data arq1_;
input alfa1 $ ind1;
datalines;
a 1
a 2
b 1
b 2
b 3
;
run;

data arq2_;
input alfa2 $ beta2 $ ind2;
datalines;
a x 1
a y 1
a y 2
a z 1
a z 2
c x 1
;
run;

/###################### FIRST METHOD #########################/

I created the table "inline" after that I just did the join with where statement ("arq_join").

proc sql;

create table inline as

select alfa2,beta2,count() as qte_alfabeta2 from arq2_ group by 1,2;quit; proc sql;create table arq_join as select alfa1, count() as qte_alfa1,
qte_alfabeta2
from
 arq1_,
 inline
where alfa1=alfa2
group by 1;
quit;

/###################### SECOND METHOD (USING INLINE) #########################/

I just put the query in the inline view, but a couldn't understand why in this case I have different results.
proc sql;
create table arq_join2 as

select a.alfa1,count()as qte_alfa1,qte_alfabeta2 from arq1_ as a, (select b.alfa2,b.beta2,count() as qte_alfabeta2
from arq2_ as b group by 1,2)
where alfa1=alfa2
group by 1;
quit;

I think it's the same query, the second method is just with inline view, isn't ?

Why the second method generates different results ?

tk's in advanced

Re: SQL - different results inline x where

PGStats — Tue, 12 Jun 2012 20:21:35 GMT

Gustavo, your question is all garbled, very difficult to read.

Re: SQL - different results inline x where

Abud — Tue, 12 Jun 2012 20:33:22 GMT

Hi Pg!!

Now, I think it's ok.

data arq1_;
input alfa1 $ ind1;
datalines;
a 1
a 2
b 1
b 2
b 3
;
run;

data arq2_;
input alfa2 $ beta2 $ ind2;
datalines;
a x 1
a y 1
a y 2
a z 1
a z 2
c x 1
;
run;

/###################### FIRST METHOD #########################/

I created the table "inline" after that I just did the join with where statement ("arq_join").

proc sql;
create table inline as select
alfa2,beta2,count(*) as qte_alfabeta2
from arq2_ group by 1,2;
quit;
proc sql;
create table arq_join as
select
alfa1,
count(*) as qte_alfa1,
qte_alfabeta2
from
arq1_,
inline
where alfa1=alfa2
group by 1;
quit;

/*###################### SECOND METHOD (USING INLINE) #########################*/

proc sql;
create table arq_join2 as
select
alfa1,
count(*) as qte_alfa1,
qte_alfabeta2
from
arq1_,
(select alfa2,beta2,count(*) as qte_alfabeta2 from arq2_ group by 1,2)
where alfa1=alfa2
group by 1
;
quit;

I think it's the same query, the second method is just with inline view, isn't ?

Why the second method generates different results ?

tk's in advanced

proc sql;
title 'arq1_';
select * from arq1_;

title 'arq2_';
select * from arq2_;

title 'inline';
select * from inline;

title 'arq_join';
select * from arq_join;

title 'arq_join2';
select * from arq_join2;

title;
quit;

Re: SQL - different results inline x where

PGStats — Tue, 12 Jun 2012 23:51:00 GMT

I'm still scratching my head over this one. I don't understand why there in no remerging in the second version. I hope someone can explain this feature :smileyconfused:

Re: SQL - different results inline x where

Abud — Wed, 13 Jun 2012 01:17:56 GMT

I know what you mean.

Tk's PG !! If you find something please send me a msg...

Re: SQL - different results inline x where

Alpay — Wed, 13 Jun 2012 14:46:34 GMT

I am not sure what's happening there in inline query.

I have added alfa2 in select statement and dropped the same in a data set option.

It seems this returns what's needed.

proc sql;

create table arq_join2(drop=alfa2) as

select

alfa1,

alfa2,

count(*) as qte_alfa1,

qte_alfabeta2

from

arq1_,

(select alfa2,beta2,count(*) as qte_alfabeta2 from arq2_ group by 1,2)

where alfa1=alfa2

group by 1

;

quit;

Re: SQL - different results inline x where

Abud — Wed, 13 Jun 2012 15:15:32 GMT

Hi,

Ttk's for the tip!!

It's working but, I think the big question is, why the second doesn't work like the first one ?

Re: SQL - different results inline x where

Howles — Wed, 27 Jun 2012 01:19:43 GMT

We are still lacking any explanation, so I submitted a streamlined version of the problem to SAS Tech Report. I will pass along what I learn.

PGStats wrote:

I'm still scratching my head over this one. I don't understand why there in no remerging in the second version. I hope someone can explain this feature

Re: SQL - different results inline x where

Howles — Thu, 19 Jul 2012 14:46:29 GMT

I meant of course to say Tech Support (not Report).

TS has responded. They say this is a bug and that it will be fixed in Version 9.4. A SAS Note will be posted once analysis of the bug has been completed.

From my own testing I perceive that a named view used in place of the inline view does not avoid the problem, but using a table to hold the intermediate result is a workaround.

Howard Schreier wrote:

We are still lacking any explanation, so I submitted a streamlined version of the problem to SAS Tech Report. I will pass along what I learn.

PGStats wrote:

I'm still scratching my head over this one. I don't understand why there in no remerging in the second version. I hope someone can explain this feature

PG

Re: SQL - different results inline x where

Howles — Sat, 04 Aug 2012 21:44:45 GMT

Tech support says that "the problem can occur when the query references a view that has an aliased aggregate on its topmost SELECT list and that alias is references on the SELECT list of the latest query but does not appear on the group by clause. It has been fixed and is currently slated to be released with SAS 9.4.".

Re: SQL - different results inline x where

econ — Sat, 04 Aug 2012 22:10:34 GMT

anytime you want to do a group by you need to do a distinct also.

puzzled me for years.

Re: SQL - different results inline x where

PGStats — Sat, 04 Aug 2012 23:30:18 GMT

That's not true. Look at the SQL solutions proposed on this forum, most uses of the GROUP BY clause occur without a DISTINCT constraint. For example, the query

SELECT a, b, SUM(x) as sumX

FROM have

GROUP BY a, b;

will only return distinct combinations of a an b. Adding a DISTINCT clause will yield the same result but less efficiently.