topic Re: Why 2 call symput stmts are needed to create a macro variable based on the row counts of a table in SAS Programming

Why 2 call symput stmts are needed to create a macro variable based on the row counts of a table

LL5 — Mon, 04 Mar 2024 14:09:12 GMT

The below code is to create a macro variable calls ROWCNT based on the number of observations from a table named MY_TABLE. The code worked and I understand what the second Call Symput statement does.

However, I try to get a full understanding on why we need to create "ROWCNT" macro variable and default to 0 before reading MY_TABLE. It'd be greatly appreciated if anyone can share some insights.

DATA _NULL_;
	CALL SYMPUT('ROWCNT',0);
	SET WORK.MY_TABLE;
	CALL SYMPUT('ROWCNT',_N_);
	RUN;
	%PUT &ROWCNT;

Re: Why 2 call symput stmts are needed to create a macro variable based on the row counts of a table

Tom — Mon, 04 Mar 2024 14:31:45 GMT

@LL5 wrote:

The below code is to create a macro variable calls ROWCNT based on the number of observations from a table named MY_TABLE. The code worked and I understand what the second Call Symput statement does.

However, I try to get a full understanding on why we need to create "ROWCNT" macro variable and default to 0 before reading MY_TABLE. It'd be greatly appreciated if anyone can share some insights.
DATA _NULL_;
	CALL SYMPUT('ROWCNT',0);
	SET WORK.MY_TABLE;
	CALL SYMPUT('ROWCNT',_N_);
	RUN;
	%PUT &ROWCNT;

It is needed to handle the case when MY_TABLE is empty. When that happens the data step will end at the SET statement and the second CALL SYMPUT() will never be called.

Also never use the ANCIENT function CALL SYMPUT(), it was supplanted by the more powerful CALL SYMPUTX() over 20 years ago. The only reason to ever use CALL SYMPUT() is if you require the generated macro variable to contain leading and/or trailing spaces. Something you DO NOT want for this application.

You can replace the first CALL with a simple %LET statement instead.

%let rowcnt=0;
DATA _NULL_;
  SET WORK.MY_TABLE;
  CALL SYMPUTX('ROWCNT',_N_);
RUN;
%PUT &=ROWCNT;

And since you are referencing and actual SAS dataset (and not a view or an external database) then use the NOBS= option of the SET statement SAS will count the obs for you and you can save a lot of time by not reading the whole dataset. Then you don't need the extra assignment to the macro variable.

DATA _NULL_;
  CALL SYMPUTX('ROWCNT',nobs);
  stop;
  SET WORK.MY_TABLE nobs=nobs;
RUN;
%PUT &=ROWCNT;

And if you do have to read all of the observations you can use the END= option of the SET statement to know when to generate the macro variable.

DATA _NULL_;
  if eof then CALL SYMPUTX('ROWCNT',_n_-1);
  SET WORK.MY_TABLE end=eof;
RUN;
%PUT &=ROWCNT;

Re: Why 2 call symput stmts are needed to create a macro variable based on the row counts of a table

LL5 — Mon, 04 Mar 2024 15:03:25 GMT

@Tom, thanks a lot for this explanation. May I ask what does the stop statement do after the call symputx and before the set statement in your second to last example?

Re: Why 2 call symput stmts are needed to create a macro variable based on the row counts of a table

Tom — Mon, 04 Mar 2024 15:09:50 GMT

@LL5 wrote:
@Tom, thanks a lot for this explanation. May I ask what does the stop statement do after the call symputx and before the set statement in your second to last example?

Just what it sounds like. It stops the data step at that point. That way you don't actually have to ever execute the SET statement. The count was already put into the NOBS variable during the compile/setup process of the data step so you only need run the CALL SYMPUTX() function and nothing else.

Re: Why 2 call symput stmts are needed to create a macro variable based on the row counts of a table

LL5 — Mon, 04 Mar 2024 15:33:26 GMT

I see. This is very helpful, really appreciated for your advice.