BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Elliott
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
data_null__
Jade | Level 19

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

14 REPLIES 14
Reeza
Super User

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
Obsidian | Level 7

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__
Jade | Level 19

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

Elliott
Obsidian | Level 7

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__
Jade | Level 19

@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
Tourmaline | Level 20

Maybe

 options nonotes;

 

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

Reeza
Super User
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
Obsidian | Level 7

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__
Jade | Level 19

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
Super User
Post what you've tried.
Elliott
Obsidian | Level 7

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__
Jade | Level 19

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
Super User
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
Obsidian | Level 7

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

 

Elliott

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 14 replies
  • 3071 views
  • 4 likes
  • 5 in conversation