Hi all SAS Users,
Today I face a problem quite strange to me. It displays MERGE even I do not have a MERGE in my statement.
The code in the log is as below
474 /*Calculating CHL for each firm per year*/
475 proc means data=work.chl_d noprint;
476 by gviidkey year;
477 var S_2DC appday;
478 id LOC;
479 Output out=chl_2d_year (keep=gviidkey year LOC CHL_2d_c appdaysum where=(appdaysum >=12))
480 mean=CHL_2d_c sum(appday)=APPDAYSUM;
483 run;
WARNING: No BY statement was specified for a MERGE statement.
Could you please help me to sort it out?
Warmest regard.
Hi @Phil_NZ,
Most likely work.chl_d is not a SAS dataset, but a DATA step view (whose instructions contain a MERGE, but no BY statement). This will become more obvious if you show the full log of the PROC MEANS step, i.e., all the notes up to
NOTE: PROCEDURE MEANS used (Total process time):
because these will contain notes referring to datasets used by the view. Or just submit
data view=work.chl_d;
describe;
run;
to reveal what's in the view.
I don't see how that could happen, but the cause is most likely because of something that came before this step. What came before?
Hi @Phil_NZ,
Most likely work.chl_d is not a SAS dataset, but a DATA step view (whose instructions contain a MERGE, but no BY statement). This will become more obvious if you show the full log of the PROC MEANS step, i.e., all the notes up to
NOTE: PROCEDURE MEANS used (Total process time):
because these will contain notes referring to datasets used by the view. Or just submit
data view=work.chl_d;
describe;
run;
to reveal what's in the view.
Hi @FreelanceReinh and @WarrenKuhfeld
Thank you for your reply, here is the log until it announces the warnings
449 /* Add one-day-ahead eta */ 450 options mergenoby=nowarn; 451 data previous_replace_/view=previous_replace_; 452 merge previous_replace previous_replace 453 (firstobs=2 keep=eta rename=(eta=eta_lead1)); 454 run; NOTE: DATA STEP view saved on file WORK.PREVIOUS_REPLACE_. NOTE: A stored DATA STEP view cannot run under a different operating system. NOTE: DATA statement used (Total process time): real time 0.01 seconds cpu time 0.00 seconds 455 options mergenoby=warn; 456 457 *Calculating s_2dc (s of two-day corrected method); 458 459 data chl_d/view=chl_d;/*chl for daily*/ 460 set previous_replace_; /* Replace the filename based on previous step*/ 461 by gviidkey datadate; 462 year=year(DATADATE); 463 if last.gviidkey then 464 eta_lead1=.; 465 S_2DC=sqrt(max(4*(c-eta)*(c-eta_lead1), 0)); 466 467 468 if missing (c) or missing (eta) or missing (eta_lead1) then 469 S_2DC=.; 470 run; NOTE: DATA STEP view saved on file WORK.CHL_D. NOTE: A stored DATA STEP view cannot run under a different operating system. NOTE: View WORK.PREVIOUS_REPLACE_.VIEW used (Total process time): real time 0.01 seconds cpu time 0.00 seconds 17 The SAS System 22:42 Thursday, March 25, 2021 NOTE: DATA statement used (Total process time): real time 0.01 seconds cpu time 0.00 seconds 471 472 473 474 /*Calculating CHL for each firm per year*/ 475 proc means data=work.chl_d noprint; 476 by gviidkey year; 477 var S_2DC appday; 478 id LOC; 479 Output out=chl_2d_year (keep=gviidkey year LOC CHL_2d_c appdaysum where=(appdaysum >=12)) 480 mean=CHL_2d_c sum(appday)=APPDAYSUM; 481 /*Explain: mean of all S_2DC of one company in a year is called CHL 482 sum SC APPDAYSUM means SC is sum of S_2DC and APPDAYSUM is sum of APPDAY*/ 483 run; WARNING: No BY statement was specified for a MERGE statement.
Thank you and warm regards,
Phil.
Thank you for your reply. Because it is a large dataset so I want to ask one further question.
So, we can fix it or we can also leave the warning but the result will not be wrong? I hope that I did not fall into a fallacy.
Warm regards.
The WARNING is a consequence of the way your MERGENOBY System Option is set.
Do
options mergenoby=nowarn;
before running the code, and reset it afterwards with
options mergenoby=warn;
to the original setting.
Thank you for your suggestion, but I thought I set the option megernoby=warn already in my code, it can be seen from the log I posted previously
449 /* Add one-day-ahead eta */
450 options mergenoby=nowarn;
451 data previous_replace_/view=previous_replace_;
452 merge previous_replace previous_replace
453 (firstobs=2 keep=eta rename=(eta=eta_lead1));
454 run;
NOTE: DATA STEP view saved on file WORK.PREVIOUS_REPLACE_.
NOTE: A stored DATA STEP view cannot run under a different operating system.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.00 seconds
455 options mergenoby=warn;
Warm regards.
The option must be set to NOWARN when the view is executed, not when it is defined.
So, voilà, a MERGE without a BY statement in the definition of DATA step view previous_replace_, which in turn is used in DATA step view chl_d. The PROC MEANS step triggers the execution of the both these DATA steps. As a result, log messages related to the DATA steps occur after the PROC MEANS code. But the notes referring to the other datasets/views (which you again didn't show) should have reminded you that DATA step views are involved.
Thank you @FreelanceReinh for your dedicated explanation. I just delete the view for the mentioned dataset then rerun the whole code, so the log just gone. It took around 2 hours so I may go to bed and will come back to the result tomorrow morning.
Warmest regards and cheers,
Phil.
Thank you for your suggestion, I test and see the chl_d was a view so I delete the view, what I received is : the warning move upto another set of datastep
After all, I am pretty sure that the warning is caused by this datastep
449 /* Add one-day-ahead eta */
450 options mergenoby=nowarn;
451 data previous_replace_/view=previous_replace_;
452 merge previous_replace previous_replace
453 (firstobs=2 keep=eta rename=(eta=eta_lead1));
454 run;
NOTE: DATA STEP view saved on file WORK.PREVIOUS_REPLACE_.
NOTE: A stored DATA STEP view cannot run under a different operating system.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds
455 options mergenoby=warn;
My guess is, because I turn on and off mergenoby here, so it does not announce warning here, but afterwards, when I work on this dataset(previous_replace_
), it announces the warning.
For example
459 data chl_d;/*chl for daily*/ 460 set previous_replace_; /* Replace the filename based on previous step*/ 461 by gviidkey datadate; 462 year=year(DATADATE); 463 if last.gviidkey then 464 eta_lead1=.; 465 S_2DC=sqrt(max(4*(c-eta)*(c-eta_lead1), 0)); 466 467 468 if missing (c) or missing (eta) or missing (eta_lead1) then 469 S_2DC=.; 470 run; WARNING: No BY statement was specified for a MERGE statement.
and
503 data chl_yc_d;/*chl for yearly corrected daily frequency*/ 504 set previous_replace_; /* Replace the filename based on previous step*/ 505 by gviidkey datadate; 506 year=year(DATADATE); 507 if last.gviidkey then 508 eta_lead1=.; 509 s_yc_d=(c-eta)*(c-eta_lead1); 510 if missing (c) or missing (eta) or missing (eta_lead1) then 511 s_yc_d=.; 512 label s_yc_d= daily chl of yearly-corrected method; 513 run; WARNING: No BY statement was specified for a MERGE statement.
Warmest regards.
So change the way you are doing the look ahead so it does not MERGE without BY.
data previous_replace_/view=previous_replace_;
set previous_replace;
set previous_replace(firstobs=2 keep=eta rename=(eta=eta_lead1)) previous_replace(obs=1 drop=_all_) ;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.