topic Re: PROC SQL with Group By Issue in SAS Procedures

PROC SQL with Group By Issue

deleted_user — Fri, 11 Jul 2008 10:55:09 GMT

I used following PROC SQL coding to retrive data from data warehouse by ODBC method. I want to group data by customer ID. However the final outcome does not show unique ID. The SAS log mentioned that "The query requires remerging summary statistics back with the original data". Does anyone have similar issue before and know how to solve it? Thanks!

PROC SQL;
CREATE TABLE d_act AS
SELECT
national_id
,snapshot_date
,Max(credit_limit_Amt) AS Cr_limit
FROM dw.ACCT
WHERE Snapshot_Date = '30JUN2008'D
GROUP BY id;

NOTE: The query requires remerging summary statistics back with the original data.

Re: PROC SQL with Group By Issue

Olivier — Fri, 11 Jul 2008 11:01:06 GMT

When SAS tells you so, that's because it found items in the SELECT part that were neither statistics, neither mentionned in the GROUP BY part.
Here you have two problems at the same time : 1) the NATIONAL_ID is typed ID in the GROUP BY (--> SQL won't connect both) and 2) the SNAPSHOT_DATE is neither a statistic, neither a GROUP BY element.
I agree that SNAPSHOT_DATE is logically constant, since it appears in the WHERE part, but that's not taken into account by SQL.
So I would try the following code :
[pre]
PROC SQL;
CREATE TABLE d_act AS
SELECT
national_id
,snapshot_date
,Max(credit_limit_Amt) AS Cr_limit
FROM dw.ACCT
WHERE Snapshot_Date = '30JUN2008'D
GROUP BY national_id, snapshot_date
;
QUIT ;
[/pre]
Regards.
Olivier

Re: PROC SQL with Group By Issue

deleted_user — Sat, 12 Jul 2008 02:29:24 GMT

Thanks Olivier for your help!