topic Re: NOTE message in SAS Programming

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

SASdevAnneMarie — Sun, 17 Dec 2023 20:39:17 GMT

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 !

Re: NOTE message

PaigeMiller — Sat, 16 Dec 2023 11:08:09 GMT

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.

Re: NOTE message

112211 — Sat, 16 Dec 2023 11:08:52 GMT

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

Re: NOTE message

SASdevAnneMarie — Sat, 16 Dec 2023 11:28:28 GMT

Thank you, I use group by, actually.

Re: NOTE message

Kurt_Bremser — Sat, 16 Dec 2023 17:06:49 GMT

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.

Re: NOTE message

Tom — Sat, 16 Dec 2023 17:22:59 GMT

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;

Re: NOTE message

SASdevAnneMarie — Sat, 16 Dec 2023 18:15:06 GMT

Thank you, Tom !