Dictionary Table Errors

[Jeff_DOC]

Good morning. I would sure appreciate any help I could get. I'm pretty new to working with dictionary tables.

I attempted to create a simple list of members in our library called VIEWSDT (all members in the library are VIEWS) using the proc SQL below. However, I come up with the error message (also below) and I'm unsure what it means or how to track the issue down and solve it. I know there are evidently some misspellings in some of the variable names, etc., however, I'm not sure how to determine what particular members contain the misspellings and/or variable type mismatches. SAS tells me they are there but not where they are. Also, the VIEWSDT.ACRS02A dataset doesn't appear to exist but SAS still thinks it does and I'm unsure how to work with this error as well. I tried looking for answers but most of what I can find is helpful on how to work with dictionary tables but not how to troubleshoot and fix them (if that's what's going on here).

I'd really appreciate some advice if someone has the time.

Thank you.

**SQL**

proc sql;
create table tables_01 as
select *
from dictionary.tables
where upcase(libname) in ('VIEWSDT')
order by libname, memname;
quit;

**ERROR MESSAGE**

WARNING: The variable creae_datae2 in the DROP, KEEP, or RENAME list has never been referenced.
ERROR: File VIEWSDT.ACRS02A.DATA does not exist.
ERROR: File VIEWSDT.ACRS02A.DATA does not exist.
ERROR: Function DATEPART requires a numeric expression as argument 1.
ERROR: Character expression requires a character format.
ERROR: Function TIMEPART requires a numeric expression as argument 1.
ERROR: Character expression requires a character format.
ERROR: Character expression requires a character format.
ERROR: Character expression requires a character format.
ERROR: The following columns were not found in the contributing tables: last_updated_when.
ERROR: File VIEWSDT.ACRS02A.DATA does not exist.
ERROR: File VIEWSDT.ACRS02A.DATA does not exist.
NOTE: Table WORK.TABLES_01 created, with 384 rows and 41 columns

[Tom]

Do you really need to query DICTIONARY.TABLES?  That has a lot of metadata fields that means SAS needs to open up every dataset or view to calculate them.  That is what is causing those problems with your views to generate notes into the log.

What information are actually trying to access?

[Jeff_DOC]

We're looking for data on these tables in order to document their attributes (since documentation on our system has never been done). I want their creation and modified dates as well as number of observations, number of variables, etc. I seem to get all that without issue except for the errors below. I assumed SAS was opening and running the views in order to determine the attributes and that's where the errors were happening, during the running process. Even if there's a better way to do it I'd still sure like to find and fix these errors. I just have no idea which view contains the error. Even with the error there are almost 400 members.

[Tom]

Try using TYPEMEM='DATA' in your WHERE clause (also no need to UPCASE() the LIBNAME as it is only stored in uppercase).

[Jeff_DOC]

Thanks Tom.

I removed the upcase but I still get the same exact errors. Sigh.

[Reeza]

> I want their creation and modified dates as well as number of observations, number of variables

Isn't the point of views a dynamic pointer, so wouldn't those be subject to change? I would assume you would need to document this by examining the code, not the tables unfortunately. I guess if you can assume that there isn't any complicated logic in the views you could go ahead but if they're undocumented in the first place how would you know that?

[Jeff_DOC]

Sorry, I'm not totally clear on what you're suggesting. One issue we are trying to fix is that the views point to a "main" library member. However, due to time constraints, laziness :), etc., there have been times a main library member is created with no associated view. We're looking to change that and so part of the project is listing all the member in each library to find disagreement. I can do that but the errors make me nervous because I'm not sure of the total effect it may have on my listings. Add to that if there are errors in my dictionary catalogs I'm not sure what the effect may be on my day-to-day operations. Errors make me nervous.

[ballardw]

I don't know if this helps but I would be tempted to use that output table you did get to drive calls to

Proc sql;

    describe view <viewname>;

quit;

for each VIEW in your table. Call execute perhaps.

That will show the code used to generate each view. So could become part of your documentation at least and hopefully find which code is referencing that problem name.

[Reeza]

Rather than query the dictionary tables, run it one by one via PROC DATASETS so you can isolate where the errors are faster and resolve them.
If your views are generating errors it means they aren't all updated or working anyways. To get the number of observations and variables the view actually has to 'execute'. Each execution can be slightly different depending on the code and exact underlying data. In a view, the number of variables and observations could change. I have concerns about the usefulness of data that doesn't reflect what would actually be in the view in the end.

[Jeff_DOC]

Thanks to all who replied and attempted to help. Several work-arounds were suggested and worked well. We did find and correct the misspelling in creae_datae2 so that did clear at least one error. The data type mismatches fixes will be a process of finding a view that works and then opening each other view to see if they agree. Just a process that we didn't even know we had going on. Really the last issue we will be left with (cross my fingers) is the File VIEWSDT.ACRS02A.DATA does not exist error. I've no idea how to fix this. I'm assuming SAS thinks it should be there and it's not. I'm going to try to do a bit more research and see if I can find any clues.

Thank you very much to everyone who tried to help.

[Tom · Accepted solution]

Sounds like one of the views it trying to access a dataset named VIEWSDT.ACRS02A.  (The extra .DATA is just something the SAS development team added to the error message to confuse the users.)

You will need to check each view to see if which one it is.

[Jeff_DOC]

Well now that's a great lead. Thank you. I'm sure it was mentioned before but I probably didn't recognize the solution. This gives me something to go on so thank you.