topic Re: Compare number of records between two datasets in SAS Programming

Compare number of records between two datasets

GeorgeBonanza — Tue, 13 Sep 2022 18:52:15 GMT

I would like to create a macro that I can use to compare the number of records between two datasets and if the number of records doesn't match then send an error to the log and stop execution of the rest of the submitted code.

The below macro works except it doesn't stop execution, I am not sure how to do that. Also I am curious to see if anyone has a more elegant solution (I am sure there are several).

%MACRO COMPARE_OBS(TBL1, TBL2);
	data _null_;
		if 0 then do; set &TBL1 nobs= obs1; set &TBL2 nobs= obs2; end;
		if obs1 ne obs2 then do;
			put "ERROR: &TBL1 and &TBL2 have different number of observations!";
		end;
		stop;
	run;
%MEND COMPARE_OBS;

Thanks in advance for your time.

Re: Compare number of records between two datasets

PaigeMiller — Tue, 13 Sep 2022 19:01:33 GMT

Are you implying that the REAL macro is actually much longer than what you are showing, and anything after this DATA _NULL_; step should not execute?

Re: Compare number of records between two datasets

GeorgeBonanza — Tue, 13 Sep 2022 19:08:52 GMT

Thank you for taking the time to respond. What I am doing is joining two tables and it should be a 1 to 1 match. I want to use the macro to check that the number of observations before and after the join are the same. If they are not the same then don't do any of the subsequent steps that follow the macro call. The rest of the steps, proc print, data step, etc., are not part of the macro.

Re: Compare number of records between two datasets

Tom — Tue, 13 Sep 2022 19:31:50 GMT

@GeorgeBonanza wrote:
Thank you for taking the time to respond. What I am doing is joining two tables and it should be a 1 to 1 match. I want to use the macro to check that the number of observations before and after the join are the same. If they are not the same then don't do any of the subsequent steps that follow the macro call. The rest of the steps, proc print, data step, etc., are not part of the macro.

So you want to create a macro variable that indicts if the two tables have the same number of observations and then use that macro variable to control later steps in the program that called the macro? So make sure the macro can return the result of its test to the calling environment.

%macro compare_obs(tbl1, tbl2,mvar);
%if not %symexist(&mvar) %then %global &mvar;
data _null_;
  call symputx("&mvar",obs1=obs2);
  stop;
  set &tbl1(drop=_all_) nobs= obs1;
  set &tbl2(drop=_all_) nobs= obs2;
run;
%mend compare_obs;

Then in the calling program test the value of the macro variable to control whether or not to execute future steps.

%compare_obs(tbl1=ds1, tbl2=ds2,mvar=same)
%if &same %then %do;
  * other steps here ;
%end;