DATA Step, Macro, Functions and more

How to get a detailed (coding) data dictionary from database?

Reply
Contributor
Posts: 30

How to get a detailed (coding) data dictionary from database?

Hello, everyone

I need to pull out a detailed coding data dictionary from my database. I tried proc contents and proc sql dictionary.tables. However, I only get a broad data dictionary. I want to know is there a way I can find a detailed coding dictionary? For example: there are numeric number 1-99 in the column TYPE in my table A. I want to know what does each number represent? I am very appreicate for any suggestions. 

 

Super User
Posts: 5,257

Re: How to get a detailed (coding) data dictionary from database?

There is not automatic tables for what you describe (if understand you correctly).

To find what values are stored in a certain column/variable, you need to query the table itself. Of the columns is indexed, you can get min/max values from the metadata (dictionary/contents etc).

What code values represent - that's a design and documentations issue. Again, no automatic function in SAS.

Data never sleeps
Super User
Posts: 5,085

Re: How to get a detailed (coding) data dictionary from database?

Sometimes the information that translates your codes 1-99 is stored in a format that is assigned to a variable.  You can print the contents of a format by adding the FMTLIB option to PROC FORMAT.  However, that same format may be applied to many variables, not just one.  The format is connected to the data by adding a FORMAT statement to a DATA step, and the translations are not considered a permanent part of the data.

Super User
Posts: 17,865

Re: How to get a detailed (coding) data dictionary from database?

How do you know what the TYPE variables mean based on your data. If you have another column that maps to it, you could run a proc freq. However, this information would be considered business knowledge and not really a technical problem in my opinion. The only impact of that is that the solution cannot be automated.
Contributor
Posts: 30

Re: How to get a detailed (coding) data dictionary from database?

Thanks everyone for your replying. My database has more than 500 tables. We do not have a detailed data dictionary. I could enter the database as a front user to check for each table and find out the code. However, this is not an efficient way. Thank you for your input and the useful informaton. 

Ask a Question
Discussion stats
  • 4 replies
  • 217 views
  • 0 likes
  • 4 in conversation