topic macro Check_DataSet_Exist in SAS Programming

macro Check_DataSet_Exist

Ronein — Wed, 19 Feb 2025 10:28:05 GMT

Hello

Please see following code.

Some questions please-

1- Is this code check if data set exist or check if sas can open the data set?

2-What is the difference between the question if data set exist and if data set can be opened? As I understand If data set exist then sas can open it and if not exist then sas cannot open it because it doesnt exist

3- What is the meaning of %let rc=%sysfunc(close(&dsid));

Why is it not written as %let rc=%sysfunc(close(&tbl));

Does it mean that we want that SAS close the open data set?

What is it beneficial to have this statement?

%macro Check_DataSet_Exist(tbl);     
%let dsid=%sysfunc(open(&tbl,i));     
%put dsid=&dsid;
%if (&dsid=0) %then  %put &tbl data set has NOT been opened;                                                                                                             
%else %put &tbl data set has been opened;                                                                                                  
%let rc=%sysfunc(close(&dsid));                                                                                                        
%mend Check_DataSet_Exist;  
%Check_DataSet_Exist(tbl=sashelp.cars) 
%Check_DataSet_Exist(tbl=sashelp.weather)

Re: macro Check_DataSet_Exist

Patrick — Wed, 19 Feb 2025 10:51:26 GMT

RTM OPEN Function and try it.

When reading the docu it's also always worth to pay some attention to the See Also section.

%let dsid=%sysfunc(open(sashelp.does_not_exist));
%put dsid=&dsid;
%if &dsid = 0 %then 
  %do;
    %put %sysfunc(sysmsg());
  %end;

What above test tells us:
The open function returns zero if it wasn't successful but it doesn't tell us why. You can retrieve the why using function sysmsg()

Re: macro Check_DataSet_Exist

Tom — Wed, 19 Feb 2025 14:03:24 GMT

For your first two questions I would say the main difference is that the EXIST() function only wants a dataset name but the OPEN() function also allows the use of dataset options.

Try these calls to the macro:

%Check_DataSet_Exist(tbl=sashelp.class)
%Check_DataSet_Exist(tbl=sashelp.class(keep=name))
%Check_DataSet_Exist(tbl=sashelp.class(keep=sam))

The last question is obvious if you read the documentation. The CLOSE() function (just like the FETCH(), VARNUM() and other VARxxxx() functions) want the dataset id that was created when the dataset was opened, not the NAME of the dataset. Check out my answer above to your first two questions to see why that is necessary.

Note it would be more logical to only call the CLOSE() function when the OPEN() worked.

%macro Check_DataSet_Exist(tbl);
%let dsid=%sysfunc(open(&tbl,i));
%put dsid=&dsid;
%if (&dsid=0) %then  %put &tbl data set has NOT been opened;
%else %do;
  %put &tbl data set has been opened;
  %let rc=%sysfunc(close(&dsid));
%end;
%mend Check_DataSet_Exist;

Re: macro Check_DataSet_Exist

ChrisHemedinger — Wed, 19 Feb 2025 14:16:09 GMT

If you want to check if a data set exists in a library, you don't need to open it. Use the EXIST function. This example will run the PROC MEANS only if SASHELP.CLASS is found.

%if %sysfunc(exist(sashelp.class)) %then %do; proc means data=sashelp.class; run; %end;

However, if you want to check for a condition where a particular variable exists, you do have to open the data set (or query the DICTIONARY tables like SASHELP.VCOLUMN -- plenty of papers/doc on that).

This example will include the variable height in the analysis only if it is found in the data set:

%macro VarExist(ds, var); %local rc dsid result; %let dsid = %sysfunc(open(&ds)); %if %sysfunc(varnum(&dsid, &var)) > 0 %then %do; %let result = 1; %put NOTE: Var &var exists in &ds; %end; %else %do; %let result = 0; %put NOTE: Var &var not exists in &ds; %end; %let rc = %sysfunc(close(&dsid)); &result %mend VarExist; proc means data=sashelp.class; var age %if %VarExist(sashelp.class, height) %then %do; height %end; ; run;

Notice that both of these examples leverage the %if/%then[/%else] ability for using macro conditional expressions in open code, which has been a capability in the language for a long time now...but people forget that and tend to wrap everything in macro routines (force of habit and probably working off decades of old examples).

Re: macro Check_DataSet_Exist

yabwon — Wed, 19 Feb 2025 14:57:52 GMT

"1- Is this code check if data set exist or check if sas can open the data set?"

The code checks if data set can be opened.

"2-What is the difference between the question if data set exist and if data set can be opened? As I understand If data set exist then sas can open it and if not exist then sas cannot open it because it doesnt exist"

You understanding is not correct.

Imagine a situation where there is a library A and there is data set B, but both you and me have access to that library. I start SAS session and I open that data set for preview. Then you start your session and do 2 things: a) you check for data set existence - data sets clearly exists, then b) you try to open it - you can't do that because the data set is already opened by me. So it is possible to have an existing data set that you cannot open.

If you modify your code snippet like this:

%macro Check_DataSet_Exist(tbl);     
%let dsid=%sysfunc(open(&tbl,i));
%put %sysfunc(sysmsg()); /* NEW LINE */ 
%put dsid=&dsid;
%if (&dsid=0) %then  %put &tbl data set has NOT been opened;                                                                                                             
%else %put &tbl data set has been opened;                                                                                                  
%let rc=%sysfunc(close(&dsid));                                                                                                        
%mend Check_DataSet_Exist;  
%Check_DataSet_Exist(tbl=sashelp.cars) 
%Check_DataSet_Exist(tbl=sashelp.weather)

you will see in the log that the reason why:

%Check_DataSet_Exist(tbl=sashelp.weather)

returned 0 is:

"ERROR: File SASHELP.WEATHER.DATA does not exist.

"3- What is the meaning of %let rc=%sysfunc(close(&dsid));"

Good programming practice that can prevent a lot of pain and stress is, after opening a data set (with the OPEN() function), to close the data set (with CLOSE() function) to "un-(b)lock" it for other users.

"Why is it not written as %let rc=%sysfunc(close(&tbl)); "

My answer is short "RTFM", documentation clearly states:

"data-set-id is a numeric variable that specifies the data set identifier that the OPEN function returns."

Link: https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/lefunctionsref/p0bbb0j41xxin4n1fdpqlf4gze0w.htm

"Does it mean that we want that SAS close the open data set?"

Yes, we want SAS to close opened data set, as I wrote above.

"What is it beneficial to have this statement?"

Without closing (previously opened) data set you may have problem with accessing it in next steps of your code.

Bart