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 !
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;
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.
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.
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;
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
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.
Ready to level-up your skills? Choose your own adventure.