Dictionary.Columns Table - became Crazy ...(unpossible to browse)

[MBKsk]

Hi,                                                                                     (location - SAS programming, SAS Administration - maybe)

my trying to get the column-list of some tab turned to fight with the table dictionary.columns ...

...when the simpliest query finished with error  - because some 'third table' doesn't contain some variable:

Proc SQL;
    create table work.MyLoveTAB_1_Structure as
       select *

        from dictionary.columns   /* >>> doesn't works ... plain query without any filters >>> ERROR: Variable AP_AMT_COMBI is not on file CRMANL.APK_CORPORATE. */
;quit;

...it is not possible to be more simply but it still fails with: ERROR: Variable AP_AMT_COMBI is not on file CRMANL.APK_CORPORATE

1. I tried to browse work library (and after> with egtask lib and another lib where the screwed tab is not located) - and it worked 

2. After this "little step for all mankind but big for me" 🙂 

 ... >>> I tried to hack him by the filters (screwed table filter out)  - and - total different error (ERROR: Incorrect length in SAS Metadata Repository for column S_CRN)

- i give up

... see code below with next both attempts to better imagine:

Proc SQL;
    create table work.MyLoveTAB_1_Structure as
       select *

        from dictionary.columns                 /* >>> doesn't works ... plain query without any filters >>>...
                                                                                >>> ERROR: Variable AP_AMT_COMBI is not on file CRMANL.APK_CORPORATE.    */
         /* (1) where libname = "WORK"             >>> it works with this one(s) */
         /*     where libname in("WORK", "EGTASK", "CCR")                        */

         /* (2) where libname <> "CRMANL" 
                  and memname <> "APK_CORPORATE"   >>> try to CHEAT him >>> :-( >>> ERROR: Incorrect length in SAS Metadata Repository for column S_CRN. */
;quit;

Have somebody any idea where is the problem?? - Database integrity -for example ... and if it's true, what should do my admin to fix it?

-thx- Martin

[Kurt_Bremser · Accepted solution]

ERROR: Incorrect length in SAS Metadata Repository for column S_CRN

Your admins need to synchronize the metadata entries with the actual metadata of the dataset(s).

Such things can happen when someone circumvents the METADATA engine for the library by issuing their own LIBNAME statement to the physical path, and then replaces a dataset which is registered in metadata. Because at that point the SAS process does not know about the metadata entries, they are not updated.

Hint for your admins: make such libraries metadata-bound.

[MBKsk]

Thank you for answer.

(1)

I'd like to make sure - is your reaction related to "Repository error massage" only?

- or when someone do as you recommend will fix whole issue (including the error: "ERROR: Variable AP_AMT_COMBI is not on file CRMANL.APK_CORPORATE")?  ..or there are some additional work-arounds to my "simple query problem" disappear?

(2)

May I something next?

...meanwhile I've found in the SAS EG (Enterprise Guide) menu\ Tools\ Update Library Metadata   >>> which generated the task "Update Metadata"

When I've marked the problematic library (CrmANL) - there was an "What to do" form as a next step:

..I wanted the report >> where I realized there are 83 "Tabs to be updated" and my trouble tab APK.. has 150 fields to update 😮 ..

BUT >> there are 3 possibilities suggested some actions (marked in red) promising more flexible update...

 + can some of them make "synchronize the metadata entries with the actual metadata of the dataset(s)" you recommended?

 + (for me) it seems the first (yellow) can to synchronize metadata with the tab, isn't it?  (or tell me pls which)

PS: possibility to refresh in selfservice style is interesting for me because our SAS admin(s) jumped about 2 years ago with no remains - so there were shifted some guys to administration (rummors: without any experiences with SAS) and since of this time we're suffering all (it would explain these 150 variables to update in problematic table). And maybe they have a similar wizard in their management tool so I can shift them the guide/instructions.

-thx- MBK

[Kurt_Bremser]

The message about the variable not found has the same reason. The metadata repository is out of sync with the "real world" (the metadata as stored in the dataset header pages).

It's best to get into contact with your SAS admins. Sometimes there are reasons why not all datasets have their metadata in the repository, so they will know how to proceed.

[Tom]

Not sure of the cause of your error message, but a word of caution on using DICTIONARY.COLUMNS (or many of the DICTIONARY psuedo tables) when you have a LOT of libraries defined.  Unless you filter by LIBNAME then EVERY library is scanned.   So any issues (like a view that is now generating errors because the variables it needs no longer exist in the sources it reads) for any dataset get reported.

So "browsing" is almost never a good idea.  So first query to get the metadata you want and then browse that result. 

Also avoid using SASHELP.VCOLUMN as it will NOT pass the WHERE condition down to the actual DICTIONARY.COLUMN query.

When writing your WHERE condition  remember that the librefs are stored in uppercase only in the dictionary tables.

proc sql;
create table work_columns as
select * from dictionary.columns
where libname='WORK'
order by libname,memname,varnum
;