topic Re: No BY statement was specified for a MERGE statement even no MERGE in SAS Programming

No BY statement was specified for a MERGE statement even no MERGE

Phil_NZ — Thu, 25 Mar 2021 10:18:41 GMT

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.

Re: No BY statement was specified for a MERGE statement even no MERGE

WarrenKuhfeld — Thu, 25 Mar 2021 10:44:50 GMT

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?

Re: No BY statement was specified for a MERGE statement even no MERGE

FreelanceReinh — Thu, 25 Mar 2021 10:45:24 GMT

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.

Re: No BY statement was specified for a MERGE statement even no MERGE

Phil_NZ — Thu, 25 Mar 2021 10:51:21 GMT

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.

Re: No BY statement was specified for a MERGE statement even no MERGE

WarrenKuhfeld — Thu, 25 Mar 2021 10:54:18 GMT

@FreelanceReinhard nailed it.

Re: No BY statement was specified for a MERGE statement even no MERGE

Phil_NZ — Thu, 25 Mar 2021 10:59:12 GMT

@WarrenKuhfeld

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.

Re: No BY statement was specified for a MERGE statement even no MERGE

FreelanceReinh — Thu, 25 Mar 2021 11:01:13 GMT

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.

Re: No BY statement was specified for a MERGE statement even no MERGE

Phil_NZ — Thu, 25 Mar 2021 11:13:34 GMT

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.

Re: No BY statement was specified for a MERGE statement even no MERGE

Tom — Thu, 25 Mar 2021 12:05:33 GMT

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;

Re: No BY statement was specified for a MERGE statement even no MERGE

Kurt_Bremser — Thu, 25 Mar 2021 12:24:09 GMT

The WARNING is a consequence of the way your MERGENOBY System Option is set.

options mergenoby=nowarn;

before running the code, and reset it afterwards with

options mergenoby=warn;

to the original setting.

Re: No BY statement was specified for a MERGE statement even no MERGE

Phil_NZ — Thu, 25 Mar 2021 18:45:10 GMT

Hi @Kurt_Bremser

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.

Re: No BY statement was specified for a MERGE statement even no MERGE

Phil_NZ — Thu, 25 Mar 2021 19:07:06 GMT

Hi @FreelanceReinh

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.

Re: No BY statement was specified for a MERGE statement even no MERGE

Kurt_Bremser — Thu, 25 Mar 2021 19:14:29 GMT

The option must be set to NOWARN when the view is executed, not when it is defined.