topic Re: How to avoid "query requires remerging summary statistics back with data in Developers

How to avoid "query requires remerging summary statistics back with data

deleted_user — Mon, 15 Oct 2007 19:08:41 GMT

I got the following message when I ran a query:
"NOTE: The query requires remerging summary statistics back with the original data."

I looked on-line in SAS Technical Support, but did not really understand what I read. Can someone "out there" please help and tell me if this is a serious error, and possible what I should do to eliminate it? Thank you so much!!

Here is my query and a preliminary data step:

data makechoice;
%let endfiscaldate = 31Aug&fiscalyr;

%let position_dates = ( DatePart(EMPLOYEE_POSITION.POSITION_END_DATE) BETWEEN "&&fiscal_date"d AND "&&endfiscaldate"d);
run;
%mend fiscal_year;

%fiscal_year;

/**********************************************************************************************
/*** Using SQL, creates the primary data table. ***
/*********************************************************************************************/

PROC SQL;
CREATE TABLE HFA033Step01 AS SELECT
EMPLOYEE.NAME as EMPLOYEE_NAME FORMAT = $40.,
EMPLOYEE_POSITION.POSITION_END_DATE,
EMPLOYEE_POSITION.POSITION_CONTRACT_TYPE_DESC as POSITION_DESC FORMAT = $40.,
EMPLOYEE_POSITION.POSITION as POSITION FORMAT = $40.,
EMPLOYEE_POSITION.POSITION_TITLE as POSITION_TITLE FORMAT = $40.,
EMPLOYEE_POSITION.POSITION_STATUS as POSITION_STATUS FORMAT = $40.,
EMPLOYEE_POSITION.ASSIGNMENT_GRADE as ASSIGNMENT_GRADE FORMAT = $40.,
EMPLOYEE_POSITION.ASSIGNMENT_SALARY_GROUP_DESC as SALARY_GROUP_DESC FORMAT = $40.,
MAX(EMPLOYEE_POSITION.ANNUAL_SALARY) as ANNUAL_SALARY,
EMPLOYEE_POSITION.POSITION_EMPLOYEE_CLASS as EMPLOYEE_CLASS FORMAT = $40.,
EMPLOYEE_POSITION.POSITION_EMPLOYEE_CLASS_DESC as EMPLOYEE_CLASS_DESC FORMAT = $40.,
POSITION_BUDGET.FISCAL_YEAR as FISCAL_YEAR,
POSITION_BUDGET.FISCAL_YEAR_START_DATE,
POSITION_BUDGET.FISCAL_YEAR_END_DATE,
POSITION_BUDGET.POSITION FORMAT = $40.
FROM ODSH.EMPLOYEE AS EMPLOYEE
LEFT JOIN ODSH.EMPLOYEE_POSITION AS EMPLOYEE_POSITION ON (EMPLOYEE.PERSON_UID = EMPLOYEE_POSITION.PERSON_UID)
LEFT JOIN ODSH.POSITION_BUDGET AS POSITION_BUDGET ON (EMPLOYEE_POSITION.POSITION = POSITION_BUDGET.POSITION)
WHERE ( EMPLOYEE.TERMINATION_DATE IS NULL ) AND
( EMPLOYEE_POSITION.POSITION_END_DATE IS NOT NULL )AND
( EMPLOYEE_POSITION.POSITION_CONTRACT_TYPE_DESC = 'Primary' ) AND
( EMPLOYEE_POSITION.POSITION_STATUS NE 'T' ) AND
( POSITION_BUDGET.FISCAL_YEAR = "&fiscalyear" ) AND
&Position_Dates
GROUP BY EMPLOYEE_NAME
ORDER BY EMPLOYEE_NAME;

QUIT;

Re: How to avoid "query requires remerging summary statistics back with data

Cynthia_sas — Mon, 15 Oct 2007 19:34:57 GMT

Hi:
In your LOG, by now, you probably have seen: NOTES, WARNINGS and ERRORS. NOTES are just that -- NOTES -- they indicate that something is happening that you may or may not be aware of. It's not an error. In your case, remerging happens usually when you use a SAS function (like SUM or MAX) without using a GROUP BY in the SQL, as described in these 2 papers:
http://www2.sas.com/proceedings/sugi28/103-28.pdf
http://www2.sas.com/proceedings/sugi29/268-29.pdf

which I found as the first 2 hits by putting the entire text of the note into a GOOGLE search box. There are also some SAS-L postings on topic that I didn't bother to put here.

If you go to here:
http://support.sas.com/techsup
and find the search box on the right hand panel (in the gold-colored section) and paste the entire note in the search box, you will come up with this hit as the first hit:
http://support.sas.com/techsup/unotes/SN/004/004308.html

cynthia

Re: How to avoid "query requires remerging summary statistics back with data

deleted_user — Mon, 15 Oct 2007 20:36:13 GMT

And thanks! Got it, read it and understood it! Yesit was due to having a MAX phrase in my PROC SQL that I took care of and fixed the note.

As always, I appreciate your help and knowledge.

Re: How to avoid "query requires remerging summary statistics back with data

Cynthia_sas — Tue, 16 Oct 2007 18:25:14 GMT

Hi,
That's good. BTW, I meant to comment on this:
[pre]

data makechoice;
%let endfiscaldate = 31Aug&fiscalyr;

%let position_dates = ( DatePart(EMPLOYEE_POSITION.POSITION_END_DATE) BETWEEN "&&fiscal_date"d AND "&&endfiscaldate"d);
run;
%mend fiscal_year;
[/pre]
I see 2 issues in the above code:
1) no SET statement
2) use of %LET instead of CALL SYMPUT.
I do NOT see a SET statement in your DATA step. So, I don't understand what you want your DATA step to do. The %LET is a compile time statement. That means, that the value of &endfiscaldate and of &position_dates will only be set ONE time. So, just FYI -- if you have a data set that has POSITION_END_DATE as a variable and you want that data set variable to be used to give values to macro variables, then you need to investigate the CALL SYMPUT statement. Using %LET in a DATA step rarely gives you the results you expect.

If you were building &endfiscaldate and &position_dates from MACRO variables (and NOT data set variables), then %LET is fine and does not need to be inside a DATA step program. -- but then the syntax of your %LET statement needs some work.

cynthia

Re: How to avoid "query requires remerging summary statistics back with data

deleted_user — Mon, 22 Oct 2007 16:42:24 GMT

Hi, Cynthia,

Apologies for not responding to your last note earlier -- was busy with SAS Server issues. I appreciate your comments and thoughts, and will put them to good use.