Macro - Varexist

[SasStatistics]

I am looking at a macro:

%macro varexist(dsname,varname);

	/* The OPEN function accesses the table and returns a 
	data set ID that is assigned to the macro variable DSID */
	
	%let dsid = %sysfunc(open(&dsname));
	
	/* The VARNUM function searches the table for the specified
	variable and returns the variable number, which is assigned
	to the macro variable VAL. If the variable is not found, VARNUM
	returns 0. */
	
	%let val = %sysfunc(varnum(&dsid,&varname));
	
	/* Use the CLOSE function to close the table */
	
	%let rc = %sysfunc(close(&dsid));
	
	/* Create macro variable EXIST that is 1 if the 
	column was found in the table and 0 otherwise. */
	
	%if &val>0 %then %do;
	    %let exist=1;
	%end;
	%else %do;
	    %let exist=0;
	%end;
	
	/* Write the value of macro variable EXIST to the 
	input stack */
	
	&exist

%mend;

Why is the line:

%let rc = %sysfunc(close(&dsid));

neccessary?

What feels strange to me is that we are creating a macro variable rc to close a table? Why would we create a macro variable for that?

[Kurt_Bremser]

You need to use the CLOSE function to clear the dataset handle, otherwise you accumulate such handles; This could lead to memory and performance problems in the long run.

It is considered good practice to always close file references, library references, file handles for datasets or external files; a macro should always leave the system in the same state that it was in before the macro executed.

Since the CLOSE function returns a result, this needs to be "caught".

The macro should be improved by adding a %LOCAL statement to avoid possible side-effects on global or "upper-scope" macro variables.

%local dsid val rc exist;

It should also include code to catch a non-existent dataset.

[andreas_lds]

The close function has a return value indicating the success of calling it, you have to capture it.

[tarheel13]

%macro doit(ds=); %IF %SYSFUNC(EXIST(&ds))=1 %THEN %DO; PROC PRINT DATA=&ds; RUN; %END;
%ELSE %DO; DATA _null_; FILE PRINT; PUT "Data set &ds does not exist."; RUN; %END; %mend;

Here is some code to capture if a dataset exists or not.

[Reeza]

How do you know that the file closed successfully?
FYI - there are various reasons a close command may not work, file is locked, file doesn't exist, was never opened etc.....

If you don't care if it opened or closed successfully then you don't need to check it.

[Tom]

The reason you should close the dataset is to clean up after yourself.

The reason you need to assign the result of the call to CLOSE() to a macro variable is because this macro is designed to work as a function. Without the %LET the result of the CLOSE() function call would be returned as part of the text string the macro generates.

The reason the macro should include a %LOCAL statement is to prevent it from overwriting the values of any already existing macro variables with the same names as the ones it is using.

You should also test if the OPEN() statement worked and avoid generating errors in the log.  What result do you want to return when the dataset doesn't exist?  True? False? Something else?

%macro varexist(dsname,varname);
%local dsid result;
%* Set default result as failure;
%let result=0;
%* Open dataset ;
%let dsid = %sysfunc(open(&dsname));
%if &dsid %then %do;	
%* Test if variable found ;
  %let result=%eval(0<%sysfunc(varnum(&dsid,&varname)));
%* Close dataset ;
  %let dsid=%sysfunc(close(&dsid));
%end;
%* Return result ;
&result.
%mend varexist;

[ChrisNZ]

>What feels strange to me is that we are creating a macro variable rc to close a table?

You use the OPEN() function, and now the data set is locked to your process (like if you open a spreadsheet it is locked).

You must CLOSE() the handle to free to data set.

Whether OPEN() takes place in a macro program or data step is irrelevant.

A macro variable needn't be created; you can close the data set in a %put statement or a %if statement if needed, or anywhere the return value (a number) is accepted.