SQL - different results inline x where

[Abud]

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<br>     from arq2_ group by 1,2;</p><p>quit;</p><p> </p><p> proc sql;</p><p>create table arq_join as<br>    select alfa1,<br>    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<br>from  arq1_ as a,<br>    (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

<span style="color: rgb(0, 0, 128); font-family: Courier New; font-size: 12pt;"><span style="color: rgb(0, 0, 128); font-family: Courier New; font-size: 12pt;"><span style="color: rgb(0, 0, 128); font-family: Courier New; font-size: 12pt;"></span></span></span> 

[PGStats]

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

PG

[Abud]

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;

[PGStats]

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:

PG

[Abud]

I know what you mean.

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

[Alpay]

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;

[Abud]

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 ?

,

[Howles]

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

[Howles]

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

[Howles]

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

[econ]

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

puzzled me for years.

[PGStats]

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.

PG