topic Re: How to get a detailed (coding) data dictionary from database? in SAS Programming

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

Yurie — Mon, 16 Nov 2015 17:04:43 GMT

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.

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

LinusH — Mon, 16 Nov 2015 17:10:51 GMT

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.

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

Astounding — Mon, 16 Nov 2015 17:16:58 GMT

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.

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

Reeza — Mon, 16 Nov 2015 20:39:51 GMT

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.

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

Yurie — Mon, 16 Nov 2015 21:08:55 GMT

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.