Turn on suggestions

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

Showing results for

- Home
- /
- Programming
- /
- Programming
- /
- Re: How do I eliminate the remerging summary data note?

Options

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

🔒 This topic is **solved** and **locked**.
Need further help from the community? Please
sign in and ask a **new** question.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 03-28-2016 02:13 PM
(3142 views)

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

14 REPLIES 14

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

@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
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Maybe

` options nonotes;`

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Post what you've tried.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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,

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

Elliott

**Don't miss out on SAS Innovate - Register now for the FREE Livestream!**

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

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.