When working with Oracle SQL I will often use a tiny virtual dataset to test some idea or function out. Ex: WITH TEMP AS ( SELECT 1 AS NUM, 'A' AS ID FROM DUAL UNION SELECT 2 AS NUM, 'B' AS ID FROM DUAL UNION SELECT 3 AS NUM, 'C' AS ID FROM DUAL UNION SELECT 4 AS NUM, 'D' AS ID FROM DUAL ) SELECT ID, SUM(NUM) AS SUMNUM FROM TEMP GROUP BY ID; I've recently been learning about proc sql's remerge feature. I wanted to test my knowledge of how it works, so I built and ran this small test case. PROC SQL;
SELECT
ID,
SUM(NUM)
FROM
(
SELECT 1 AS NUM, 'A' AS ID FROM SASHELP.AIR
UNION
SELECT 2 AS NUM, 'B' AS ID FROM SASHELP.AIR
UNION
SELECT 3 AS NUM, 'C' AS ID FROM SASHELP.AIR
UNION
SELECT 4 AS NUM, 'D' AS ID FROM SASHELP.AIR
);
QUIT; Output: This result was unexpected. I guessed that the cause of this unexpected output was the 'fake' table. To test this theory I wrote the virtual table to a dataset and used that dataset to rerun the proc sql query. PROC SQL;
CREATE TABLE TEMP AS
SELECT 1 AS NUM, 'A' AS ID FROM SASHELP.AIR
UNION
SELECT 2 AS NUM, 'B' AS ID FROM SASHELP.AIR
UNION
SELECT 3 AS NUM, 'C' AS ID FROM SASHELP.AIR
UNION
SELECT 4 AS NUM, 'D' AS ID FROM SASHELP.AIR;
SELECT
ID,
SUM(NUM)
FROM
TEMP;
QUIT; Output: This is the output I expected from the first query. I've exhausted my google-fu and can't find anything that explains why this occurs. This is an edge case that I can't see ever coming up in my development work, but it still bugs me. Does anyone know why this happens or where I could find more information about the way SAS handles remerging? SAS system information: SAS Enterprise Guide 7.12 (7.100.2.3350) (64-bit) Base SAS Software Custom version information: 9.4_M3 Image version information: 9.04.01M3P060315
... View more
