topic Re: NOTE: The query requires remerging summary statistics back with the original data. in SAS Programming

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

frupaul — Sun, 04 Nov 2018 20:39:33 GMT

Hi Everyone,

I am trying to summaries data contained in sashelp.class using PROC SQL to a report similar (column headers might be different but information contained should match) to the below output generated from PROC REPORT:

The script I have written is this:

proc sql;
select 
     age, 
     case when Sex='F' then sum(Weight) else . end as Weight_Femal, 
     case when Sex='M' then sum(weight) else . end as Weight_Male
from sashelp.class
group by 1;
quit;

However, this generates the error message:

I know what the error is, but in this case i dont know how to correct it. Can anyone help please?

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

ChrisNZ — Sun, 04 Nov 2018 20:46:37 GMT

Like this?

proc sql;
  select age
        ,sum(case when Sex='F' then Weight else . end) as Weight_F
        ,sum(case when Sex='M' then Weight else . end) as Weight_M 
  from sashelp.class
  group by 1;
quit;

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

ChrisNZ — Sun, 04 Nov 2018 22:30:17 GMT

You cam also write this as:

proc sql;
  select AGE
        ,sum((SEX='F') * WEIGHT) as WEIGHT_F
        ,sum((SEX='M') * WEIGHT) as WEIGHT_M
  from SASHELP.CLASS
  group by 1;
quit;