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

[SASdevAnneMarie]

Hello Experts,

I have a NOTE message in my log : 

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

Do you know please why we can have it in proc sql ?

Could I continue to run my program with this message (the result is OK).

Thank you !

[PaigeMiller]

Please do not show us partial logs. From now on (including this thread), please do show us the ENTIRE log for this PROC that you have a question about, that's every single line in the log for this PROC.

[112211]

Dont want that note Use (Group by) instead of (having) keyword

[SASdevAnneMarie]

Thank you, I use group by, actually.

[Kurt_Bremser]

Always show your whole log, copy/paste it into a window opened with this button:

Always. Always. ALWAYS.

The NOTE is caused by using a variable in the SELECT which is neither part of the GROUP BY clause nor the result of a SQL summary function. In most SQL variants (as used in databases), this would cause an ERROR, but SAS allows it.

[Tom · Accepted solution]

If you select detail variables that are neither grouping variables nor summary statistics then SAS will remerge the summary statistics onto all of the observations in the group.

1    proc sql ;
2    create table want as
3      select *,mean(age) as mean_age_by_sex
4      from sashelp.class
5      group by sex
6      order by name
7    ;
NOTE: The query requires remerging summary statistics back with the original data.
NOTE: Table WORK.WANT created, with 19 rows and 6 columns.

8    quit;

A lot of SQL implementations will not remerge summary statistics for you.

I assume that is why SAS issues the note.

You don't need to change anything unless that is NOT what you intended.

But if you want to do the same thing in some other implementation of SQL you might need to make your query more complicated.

9    proc sql ;
10   create table want as
11     select a.*,b.mean_age_by_sex
12     from sashelp.class a
13     inner join
14      (select sex,mean(age) as mean_age_by_sex
15       from sashelp.class group by sex) b
16     on a.sex=b.sex
17     order by name
18   ;
NOTE: Table WORK.WANT created, with 19 rows and 6 columns.

19   quit;

[SASdevAnneMarie]

Thank you, Tom !