Learning SAS BI

Getting Started with SAS BI:

SAS® Business Intelligence Client Tools Course Notes (SBIOVR) - This Course Notes is a wonderful material to learn/ practice SAS BI

Weird Error from Dictionary Tables (Migration issues of SAS 9.1 to 9.2)

/*Weird Error from Dictionary Tables during SAS Upgrade*/
proc sql;
select * from dictionary.TABLES;
quit;
proc sql;
select * from sashelp.VCOLUMN; *This internally query’s DICTIONARY.COLUMNS
quit;


ERROR: File WORK.XYZ.DATA does not exist.
ERROR: File WORK.XYZ.DATA does not exist.


Reason behind this problem in our case –


There was a table named XYZ in library ABC but this had a input table from a second library that had not been assigned (i.e., WORK). The WORK Library was not assigned because in a grid enabled SAS environment the WORK library is reassigned as another library (named RMTWORK). As the ABC.XYZ was somehow reading the data from WORK.XYZ it resulted in a weird error. The below link states a similar reason for this problem (in a Note) –

Note: An error occurs if DICTIONARY.TABLES is used to retrieve information about an SQL view that exists in one library but has an input table from a second library that has not been assigned.


http://support.sas.com/documentation/cdl/en/sqlproc/62086/HTML/default/viewer.htm#a001385596.htm

How to avoid this error -

We can avoid this error by eliminating the problematic libraries (i.e., in our case it is ABC & WORK). If we filer out ABC & WORK Library in where condition, the ABC & WORK Library data will not even be loaded to SAS & hence it does not throw error. You can find similar problem faced by another SAS User in the below link –


http://www.listserv.uga.edu/cgi-bin/wa?A2=ind0907d&L=sas-l&P=22255


/*Fix for Query*/
proc sql;
select * from dictionary.TABLES
where libname not in ("ABC", "WORK");
quit;
proc sql;
select * from sashelp.VCOLUMN
where libname not in ("ABC", "WORK");
quit;

The solution given here explains only about "how to avoid the error?” as the original query is successful even with errors.