turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- How do I eliminate the remerging summary data note...

Topic Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

Accepted Solutions

Solution

04-03-2016
03:25 PM

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Elliott

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;
```

All Replies

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Elliott

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Reeza

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.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Elliott

03-28-2016 02:35 PM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to data_null__

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,

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Elliott

03-28-2016 03:09 PM - edited 03-29-2016 10:46 AM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Elliott

03-28-2016 09:24 PM

Maybe

` options nonotes;`

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Elliott

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.

SAS does this automatically, thats what the note indicates.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Reeza

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?

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Elliott

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.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Elliott

03-28-2016 03:11 PM

Post what you've tried.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Reeza

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,

Solution

04-03-2016
03:25 PM

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Elliott

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;
```

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Elliott

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;

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Elliott

03-31-2016 04:12 PM

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

Elliott