DATA Step, Macro, Functions and more

How do I eliminate the remerging summary data note?

Accepted Solution Solved
Reply
Contributor
Posts: 69
Accepted Solution

How do I eliminate the remerging summary data note?

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


Accepted Solutions
Solution
‎04-03-2016 03:25 PM
Respected Advisor
Posts: 3,777

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

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


All Replies
Super User
Posts: 17,829

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

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 Smiley Sad

Contributor
Posts: 69

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

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.

Respected Advisor
Posts: 3,777

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

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

Contributor
Posts: 69

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

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,

Respected Advisor
Posts: 3,777

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

[ Edited ]

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

 

PROC Star
Posts: 1,561

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

Maybe

 options nonotes;

 

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

Super User
Posts: 17,829

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

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.
Contributor
Posts: 69

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

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?

Respected Advisor
Posts: 3,777

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

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

 

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

Super User
Posts: 17,829

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

Post what you've tried.
Contributor
Posts: 69

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

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,

Solution
‎04-03-2016 03:25 PM
Respected Advisor
Posts: 3,777

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

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;
Super User
Posts: 9,681

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

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;





Contributor
Posts: 69

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

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

 

Elliott

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 14 replies
  • 738 views
  • 4 likes
  • 5 in conversation