Solved: Re: How do I eliminate the remerging summary data note?

Elliott · Posted 03-28-2016 02:13 PM

I need to get the same output from the attached code sample but need to eliminate the remerging summary data note in the log, is there a way this can be done? I am testing in SAS Enterprise Guide 6.1 running on SAS GRID environment but code will ultimately be executed from a unix shell script.

Sample Code:

data have ;

input wod mmddyy10. type $ rptid $ sum_errors sum_denom ;

cards;

12/20/2015 CARS RPT1 123 456

12/20/2015 CARS RPT2 0 178

12/20/2015 CARS RPT3 0 1029

12/20/2015 CACARS RPT1 0 1848

12/20/2015 CACARS RPT2 0 2558

12/20/2015 CACARS RPT3 0 86

12/21/2015 CARS RPT1 0 654

12/21/2015 CARS RPT2 1 781

12/21/2015 CARS RPT3 1 219

12/21/2015 CACARS RPT1 1 1484

12/21/2015 CACARS RPT2 1 8255

12/21/2015 CACARS RPT3 1 98

12/22/2015 CARS RPT1 10 654

12/22/2015 CARS RPT2 12 871

12/22/2015 CARS RPT3 11 129

12/22/2015 CACARS RPT1 5 1484

12/22/2015 CACARS RPT2 10 255

12/22/2015 CACARS RPT3 7 68

run;

/* creates the max and min of errors and denom */ /*remerging note on this table*/

PROC SQL;

create table xyz_3day_max_min as

select wod, type, rptid,

MAX(sum_errors) as max_errs,

MIN(sum_errors) as min_errs,

MAX(sum_denom) as max_denom,

MIN(sum_denom) as min_denom

from have

group by 2,3

order by 1,2,3;

quit;

/* consolidates above table to one record per report */

proc sql;

create table xyz_max_min as

select distinct type, rptid, max_errs, min_errs, max_denom, min_denom

from xyz_3day_max_min

;

quit;

LOG:

NOTE: The data set WORK.HAVE has 18 observations and 5 variables.

NOTE: Compressing data set WORK.HAVE increased size by 100.00 percent.

Compressed is 2 pages; un-compressed would require 1 pages.

NOTE: DATA statement used (Total process time):

real time 0.01 seconds

cpu time 0.00 seconds

44 run;

45

46

47 /* creates the max and min of errors and denom */ /*remerging note on this table*/

48 PROC SQL;

49 create table xyz_3day_max_min as

50 select wod, type, rptid,

51 MAX(sum_errors) as max_errs,

52 MIN(sum_errors) as min_errs,

53 MAX(sum_denom) as max_denom,

54 MIN(sum_denom) as min_denom

55 from have

56 group by 2,3

57 order by 1,2,3;

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

NOTE: Compressing data set WORK.XYZ_3DAY_MAX_MIN increased size by 100.00 percent.

Compressed is 2 pages; un-compressed would require 1 pages.

NOTE: Table WORK.XYZ_3DAY_MAX_MIN created, with 18 rows and 7 columns.

58

59 quit;

NOTE: PROCEDURE SQL used (Total process time):

real time 0.03 seconds

2 The SAS System 11:56 Monday, March 28, 2016

cpu time 0.02 seconds

60

61 /* consolidates above table to one record per report */

62 proc sql;

63 create table xyz_max_min as

64 select distinct type, rptid, max_errs, min_errs, max_denom, min_denom

65 from xyz_3day_max_min

66 ;

NOTE: Compressing data set WORK.XYZ_MAX_MIN increased size by 100.00 percent.

Compressed is 2 pages; un-compressed would require 1 pages.

NOTE: Table WORK.XYZ_MAX_MIN created, with 6 rows and 6 columns.

67 quit;

NOTE: PROCEDURE SQL used (Total process time):

real time 0.02 seconds

cpu time 0.00 seconds

Thanks,

Elliott

data_null__ · Posted 03-28-2016 03:56 PM

I think this works.

data have ;
input wod mmddyy10. type $ rptid $ sum_errors sum_denom ;
cards;
12/20/2015 CARS RPT1 123 456
12/20/2015 CARS RPT2 0 178
12/20/2015 CARS RPT3 0 1029
12/20/2015 CACARS RPT1 0 1848
12/20/2015 CACARS RPT2 0 2558
12/20/2015 CACARS RPT3 0 86
12/21/2015 CARS RPT1 0 654
12/21/2015 CARS RPT2 1 781
12/21/2015 CARS RPT3 1 219
12/21/2015 CACARS RPT1 1 1484
12/21/2015 CACARS RPT2 1 8255
12/21/2015 CACARS RPT3 1 98
12/22/2015 CARS RPT1 10 654
12/22/2015 CARS RPT2 12 871
12/22/2015 CARS RPT3 11 129
12/22/2015 CACARS RPT1 5 1484
12/22/2015 CACARS RPT2 10 255
12/22/2015 CACARS RPT3 7 68
run;
 
 
/* creates the max and min of errors and denom */ /*remerging note on this table*/
PROC SQL;
     create table xyz_3day_max_min as
          select wod, type, rptid,
               MAX(sum_errors) as max_errs,
               MIN(sum_errors) as min_errs,
               MAX(sum_denom) as max_denom,
               MIN(sum_denom) as min_denom
          from have
          group by 2,3
          order by 1,2,3;
 
quit;

/* creates the max and min of errors and denom */ /*no remerging note on this table*/
PROC SQL;
   create view summary as
   select distinct type, rptid,
         MAX(sum_errors) as max_errs,
         MIN(sum_errors) as min_errs,
         MAX(sum_denom) as max_denom,
         MIN(sum_denom) as min_denom
         from have 
   group by type, rptid;
   
   create table test as select a.*,b.max_errs,b.min_errs,b.max_denom,b.min_denom
      from have(drop=sum_:) as a, summary as b
      where a.type eq b.type and a.rptid eq b.rptid
      order by 1,2,3;
   quit;
   run;
proc compare base=xyz_3day_max_min compare=test;
   run;

View solution in original post

Reeza · Posted 03-28-2016 02:16 PM

You need to ensure that your all variables in your GROUP BY are listed in your select and only summary functions beside.

Unfortunately this will mean adding in a manual merge step instead 😞

Elliott · Posted 03-28-2016 02:28 PM

I have tried adding wod to the group by but I do not get the same result. I have tried everything I can think of and everything I googled and cannot seem to get the same result without the note.

data_null__ · Posted 03-28-2016 02:35 PM

What is the harm of the NOTE:. It is afterall what SQL is doing.

Elliott · Posted 03-28-2016 02:58 PM

Makes no sence to me either but our data mgmt team won't install in schedule unless the log is clear of "unacceptable notes" and this is one of them. So I am trying to figure out a way to recode and get the same result.

Thanks,

data_null__ · Posted 03-28-2016 03:09 PM

@Elliott wrote:

Makes no sence to me either but our data mgmt team won't install in schedule unless the log is clear of "unacceptable notes" and this is one of them. So I am trying to figure out a way to recode and get the same result.

Thanks,

EDIT: I've learnt that this may indicate that SQL did something that you may not expect and should not ignored.

See this Usage note: http://support.sas.com/techsup/notes/v8/4/308.html

And this SAS-L thread answer by folks that know what their talking about.

https://listserv.uga.edu/cgi-bin/wa?A2=SAS-L;cca7a5b6.1603e

ChrisNZ · Posted 03-28-2016 09:24 PM

Maybe

 options nonotes;

for this step will avoid mgmt getting their knickers in a twist?

High-Performance SAS Coding - Third Edition

Reeza · Posted 03-28-2016 02:56 PM

To get rid of note you need to break up steps. Calculate summary stats in one SQL step and merge back with data in second SQL step.

SAS does this automatically, thats what the note indicates.

Elliott · Posted 03-28-2016 03:08 PM

I have tried breaking up the steps but I either still get the note or I don't get the correct result. Can you provide an example of how it should be done?

data_null__ · Posted 03-28-2016 03:11 PM

Just use PROC SUMMARY and MERGE the stats back in a data step.

No one will ever know you did exactly the same thing.

Reeza · Posted 03-28-2016 03:11 PM

Post what you've tried.

Elliott · Posted 03-28-2016 03:57 PM

I did not save code I had tried, I have been working on this for a few weeks and have not had any success, that is why I posted here.

Thanks,

data_null__ · Posted 03-28-2016 03:56 PM

I think this works.

data have ;
input wod mmddyy10. type $ rptid $ sum_errors sum_denom ;
cards;
12/20/2015 CARS RPT1 123 456
12/20/2015 CARS RPT2 0 178
12/20/2015 CARS RPT3 0 1029
12/20/2015 CACARS RPT1 0 1848
12/20/2015 CACARS RPT2 0 2558
12/20/2015 CACARS RPT3 0 86
12/21/2015 CARS RPT1 0 654
12/21/2015 CARS RPT2 1 781
12/21/2015 CARS RPT3 1 219
12/21/2015 CACARS RPT1 1 1484
12/21/2015 CACARS RPT2 1 8255
12/21/2015 CACARS RPT3 1 98
12/22/2015 CARS RPT1 10 654
12/22/2015 CARS RPT2 12 871
12/22/2015 CARS RPT3 11 129
12/22/2015 CACARS RPT1 5 1484
12/22/2015 CACARS RPT2 10 255
12/22/2015 CACARS RPT3 7 68
run;
 
 
/* creates the max and min of errors and denom */ /*remerging note on this table*/
PROC SQL;
     create table xyz_3day_max_min as
          select wod, type, rptid,
               MAX(sum_errors) as max_errs,
               MIN(sum_errors) as min_errs,
               MAX(sum_denom) as max_denom,
               MIN(sum_denom) as min_denom
          from have
          group by 2,3
          order by 1,2,3;
 
quit;

/* creates the max and min of errors and denom */ /*no remerging note on this table*/
PROC SQL;
   create view summary as
   select distinct type, rptid,
         MAX(sum_errors) as max_errs,
         MIN(sum_errors) as min_errs,
         MAX(sum_denom) as max_denom,
         MIN(sum_denom) as min_denom
         from have 
   group by type, rptid;
   
   create table test as select a.*,b.max_errs,b.min_errs,b.max_denom,b.min_denom
      from have(drop=sum_:) as a, summary as b
      where a.type eq b.type and a.rptid eq b.rptid
      order by 1,2,3;
   quit;
   run;
proc compare base=xyz_3day_max_min compare=test;
   run;

Ksharp · Posted 03-28-2016 10:31 PM

Use sub-query instead. But that is inefficient .



data have ;
input wod mmddyy10. type $ rptid $ sum_errors sum_denom ;
cards;
12/20/2015 CARS RPT1 123 456
12/20/2015 CARS RPT2 0 178
12/20/2015 CARS RPT3 0 1029
12/20/2015 CACARS RPT1 0 1848
12/20/2015 CACARS RPT2 0 2558
12/20/2015 CACARS RPT3 0 86
12/21/2015 CARS RPT1 0 654
12/21/2015 CARS RPT2 1 781
12/21/2015 CARS RPT3 1 219
12/21/2015 CACARS RPT1 1 1484
12/21/2015 CACARS RPT2 1 8255
12/21/2015 CACARS RPT3 1 98
12/22/2015 CARS RPT1 10 654
12/22/2015 CARS RPT2 12 871
12/22/2015 CARS RPT3 11 129
12/22/2015 CACARS RPT1 5 1484
12/22/2015 CACARS RPT2 10 255
12/22/2015 CACARS RPT3 7 68
run;
 
 
proc sql;
create table want as
  select wod, type, rptid,
   (select MAX(sum_errors) from have where type=a.type and rptid=a.rptid) as max_errs,
   (select MIN(sum_errors) from have where type=a.type and rptid=a.rptid) as min_errs,
   (select MAX(sum_denom) from have where type=a.type and rptid=a.rptid) as max_denom,
   (select MIN(sum_denom) from have where type=a.type and rptid=a.rptid) as min_denom
   from have as a
     order by 1,2,3;
quit;

Elliott · Posted 03-31-2016 04:12 PM

Thank you all for your help. I was able to eliminate the note from the log.

Elliott

SAS Innovate 2025: Register Now

SAS Training: Just a Click Away