BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
SASdevAnneMarie
Barite | Level 11

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 !

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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;

View solution in original post

6 REPLIES 6
PaigeMiller
Diamond | Level 26

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.

--
Paige Miller
112211
Obsidian | Level 7
Dont want that note Use (Group by) instead of (having) keyword
SASdevAnneMarie
Barite | Level 11
Thank you, I use group by, actually.
Kurt_Bremser
Super User

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

Bildschirmfoto 2020-04-07 um 08.32.59.jpg

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
Super User Tom
Super User

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
Barite | Level 11
Thank you, Tom !

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 846 views
  • 2 likes
  • 5 in conversation