In SAS^® 9.4, the SQL procedure fails to remerge summary statistics occurs when queries contain all of the following conditions:

• A join of a table and an in-line view
• A non-summary function in the SELECT/HAVING clauses is not referenced in the GROUP BY clause
• A column alias or a column modifier is applied

In SAS^® 9.3 and earlier releases, remerging does not occur under these conditions with or without the column alias or column modifier.

To work around this issue, drop the alias or column modifier in the outer query.

Operating System and Release Information

Product Family	Product	System	Product Release		SAS Release
			Reported	Fixed*	Reported	Fixed*
SAS System	Base SAS	z/OS	9.4		9.4 TS1M0
		z/OS 64-bit	9.4		9.4 TS1M0
		Microsoft® Windows® for x64	9.4		9.4 TS1M0
		Microsoft Windows 8 Enterprise x64	9.4		9.4 TS1M0
		Microsoft Windows 8 Pro x64	9.4		9.4 TS1M0
		Microsoft Windows 8.1 Enterprise 32-bit	9.4		9.4 TS1M0
		Microsoft Windows 8.1 Enterprise x64	9.4		9.4 TS1M0
		Microsoft Windows 8.1 Pro 32-bit	9.4		9.4 TS1M0
		Microsoft Windows 8.1 Pro x64	9.4		9.4 TS1M0
		Microsoft Windows 10	9.4		9.4 TS1M0
		Microsoft Windows Server 2008 R2	9.4		9.4 TS1M0
		Microsoft Windows Server 2008 for x64	9.4		9.4 TS1M0
		Microsoft Windows Server 2012 Datacenter	9.4		9.4 TS1M0
		Microsoft Windows Server 2012 R2 Datacenter	9.4		9.4 TS1M0
		Microsoft Windows Server 2012 R2 Std	9.4		9.4 TS1M0
		Microsoft Windows Server 2012 Std	9.4		9.4 TS1M0
		Windows 7 Enterprise x64	9.4		9.4 TS1M0
		Windows 7 Professional x64	9.4		9.4 TS1M0
		64-bit Enabled AIX	9.4		9.4 TS1M0
		64-bit Enabled Solaris	9.4		9.4 TS1M0
		HP-UX IPF	9.4		9.4 TS1M0
		Linux for x64	9.4		9.4 TS1M0
		Solaris for x64	9.4		9.4 TS1M0

* For software releases that are not yet generally available, the Fixed Release is the software release in which the problem is planned to be fixed.

In the code below, the first query remerges because the code does not include a column modifier. The second query incorrectly fails to remerge because the code includes the format column modifier.

---

data one;
input id ind_count type $;
cards;
1 1 X
2 1 X
2 2 X
2 3 X
;
run;

 proc sql;
 create table two
 as select a.id
          ,b.ind_count  /* <=== No column modifier applied */
          ,count(*) as Tot_count
 from  one  as a,
        (select id
            ,count(*)  as ind_count
        from  one
        where type = 'X'
        group by id
        ) as b
where a.id = b.id
group by a.id ;
quit;


 proc sql;
 create table three
 as select a.id
          ,b.ind_count format=8. /* <=== column modifier added */
          ,count(*) as Tot_count
 from  one  as a,
        (select id
               ,count(*)  as ind_count
        from  one
        where type = 'X'
        group by id
        ) as b
where a.id = b.id
group by a.id ;
quit;

Type:	Usage Note
Priority:	high

Date Modified:	2017-04-17 12:36:46
Date Created:	2017-03-13 14:33:36