Determine if a SAS dataset still exists in a SAS library

[sassy7]

Hello,  

The program I am executing read from the dataset Dictionary.tables, several datasets and views available in the selected library but in the SAS logs there are error messages because not all the datasets behind the views could be located.

ERROR: SQL View XYZ could not be processed because at least one of the
data sets, or views, referenced directly (or indirectly) by it could not be located, or opened
successfully.

The SAS views were created in the past years and then the datasets behind the views were moved in the backup folders and deleted from libraries.

Also:

• tried to obtain a list of the "existing" views with proc datasets but the results are similar (ie error messages for non-existing views).
• tried with SAS macro function: ie %sysfunc(exist(&dsn., VIEW)), but it does not work in this case, namely the views exist.

The program should be a part of a scheduled SAS job but error messages makes the program execution stops.

Please, could you suggest a SAS function/proc sql statement or option that is able to prove whether a SAS view exists(aka the datasets behind the views still exist)?

[ballardw]

Were the views created by Proc SQL, data step or both?

Recovering the description is different depending on the source.

If the Source=Encrypt option was used to create the view then you may be out of luck as far as recovering the descriptions at all.

Also if the view was created by a data step it might involve different sources such as reading an external text file with input statements not just data sets that may have been moved to backup.

If a view was created by a data step the syntax like this will send a description to the view of the data step code: (Caveat: if the code was written in an enough different version of SAS your current system may return errors about creation on a incompatible host and remain unavailable)

data view=libname.viewname;
   describe;
run;

If the view was created by Proc Sql:

proc sql;
   describe view libname.viewname;
quit;

These send the output to the LOG.  So there would have to be steps involving parsing the logs to do something which is always a data problematic.

I am not sure you have clearly stated you goal. If it is to have your existing program run then are you looking to

1) exclude those views from processing so there are no errors to report

2) remove those views from the library

3) restore the data sets from the backup so the views will behave

4) something else

[sassy7]

Hello Ballardw,

Thanks a lot for the info provided but my scope is 1) exclude those views from processing so there are no errors to report.

[ballardw]

If you want help to automagically not process the views you will need to share what sort of code you are currently using so we can identify places in your code where additional processing and type would go.

If this is a recurring process then I would suggest some planning with whoever moves those data sets so that the appropriate views can be removed at the same time. If you no longer have the code creating the views then the steps above and the comments about parsing the logs could be used to build a control data set with the View <=> dataset relationships and use that to use FEXIST on the data sets as a step in the process. Update that control data set each time you add a view.

[Tom]

You must have SQL views and not data step views.  With data step views you do not get errors from either PROC CONTENTS or DICTIONARY.COLUMNS.

Try it:

proc datasets lib=work nolist kill; run;
data class; set sashelp.class; run;
data class_v / view=class_v; set class; run;
proc delete data=class; run;

proc contents data=work._all_ varnum; run;
proc sql;
 select memname,varnum,name,type,length 
 from dictionary.columns
 where libname='WORK'
 ;
quit;

Now replace the data step view with an SQL view instead:

proc sql; create view class_v as select * from class; quit;

And you get errors:

872  proc contents data=work._all_; run;

ERROR: File WORK.CLASS.DATA does not exist.
ERROR: SQL View WORK.CLASS_V could not be processed because at least one of the data sets, or views, referenced directly (or
       indirectly) by it could not be located, or opened successfully.
NOTE: Statements not processed because of errors noted above.
NOTE: PROCEDURE CONTENTS used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds

NOTE: The SAS System stopped processing this step because of errors.


873  proc sql;
874   select memname,varnum,name,type,length
875   from dictionary.columns
876   where libname='WORK'
877   ;
ERROR: File WORK.CLASS.DATA does not exist.
NOTE: No rows were selected.
878  quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds

But you do not get any error when using DICTIONARY.MEMBERS.  So you can first check whether the member is a VIEW or an actual data step and adjust your code appropriately.

For example to see the variables in the actual datasets you could use:

proc sql ;
 select * 
 from dictionary.columns
 where libname='WORK' and memtype='DATA'
 ;
quit;

[SASKiwi]

What do you plan to do in your scheduled jobs if a required dataset doesn't exist? Use different code and datasets instead? Report on the missing datasets? If it is the latter then you could just parse the log to collect the errors instead - see PROC SCAPROC.