BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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;
4 REPLIES 4
Cynthia_sas
SAS Super FREQ
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
deleted_user
Not applicable
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.
Cynthia_sas
SAS Super FREQ
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
deleted_user
Not applicable
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.

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
  • 4 replies
  • 27325 views
  • 2 likes
  • 2 in conversation