topic Re: How to display complete data dictionary in New SAS User

How to display complete data dictionary

Helal — Mon, 29 Apr 2019 20:10:14 GMT

I am fairly new to using SAS and have a project to upload 100 SAS data sets to SQL server with their meta data or their complete data dictionaries. I am using SAS 9.4. I transferred SAS data sets to SQL as only raw data files with no data labels. So, I need to extract a comprehensive data dictionary for all data sets in the library. The dictionary includes at least variable/column names, variable/column labels, value labels, variable/column type. Example of displaying variable Gender is:

Variable/Column name = GenderTyp

Variable/Column Label=GenderTyp

Value Labels = 2='Female' 1= 'Male'

Variable/Column Type=Char

Proc content will give me all info I need but Value Labels. Same with Proc SQL; select dictionary.column;

How do I get Value labels? Attached file shows here is how Gender is stored in the demographic data set.

Thank you,

Re: How to display complete data dictionary

Reeza — Mon, 29 Apr 2019 20:56:08 GMT

Assuming you mean formats, you can use the SASHELP.VTABLE for table meta data, VCOLUMN for column level metadata and VFormats for the format information. For the specific format definitions you can extract them using PROC FORMAT with a CNTLOUT data set.

Re: How to display complete data dictionary

Helal — Tue, 30 Apr 2019 17:47:01 GMT

Thank you Reeza.

Re: How to display complete data dictionary

Helal — Tue, 02 Jul 2019 02:44:06 GMT

Hi Reeza,

I am still struggling to get values or value labels off my SAS data sets. Using your suggestion for a single variable in the data set will give me the followings. First here is the code:

Proc SQL;
select *
from sashelp.vcolumn
where libname="CENCD" and name='Anger_37_C';
Quit;

The output is attached (Figure 1). The only info I need for this variable is Column Name and Label+ value(or valuer labels). If I run a simple Frequency for this variable, I get the output (Figure 2 in the attached Excel file). So, my question is how can I get something like this:

Name Labels Value

Anger_37_C TBIQOL Anger: became verbally abusive? Always, Missing, Never, Often, Rarely, Sometimes

For all variable sin the data set. In the case of this variable, it looks like there is no actual Value Labels like 1= Always, 2=Sometimes, etc....rather it's the actual Character. I hope my question is clear.

Thank You

Re: How to display complete data dictionary

Reeza — Tue, 02 Jul 2019 15:36:12 GMT

What you're calling variable 'labels' here is usually referred to as a SAS format. Did you try using CNTLOUT to get the formats or VFORMAT table to get the format? It will require a second query to know which format you want. But you'll have to consider which formats you want. I *think* there's a category in vformats that differentiates user defined format from a system format, ie CHAR is a system format, whereas CL_YESNO. is a custom format.

But maybe this macro will help you instead of you writing your own custom code?
https://communities.sas.com/t5/SAS-Communities-Library/Library-Datasets-Summary-Macro-DATA-SPECS/ta-p/544757

I have not tried it or used it so cannot comment beyond that. HTH.

Re: How to display complete data dictionary

Helal — Tue, 02 Jul 2019 23:25:11 GMT

If I understood correctly, I have to have Format files in order to use vformat and cntlout but there no format files. The macro has helped though except it produces observations as well that I don't need and I am not savvy to change the macro and get remove the observation piece. Anyway, thank you for sharing the macro with me.

Re: How to display complete data dictionary

Reeza — Wed, 03 Jul 2019 01:25:42 GMT

Go into the macro program (.sas). Comment out the proc datasets at the end and then you'll get datasets in your work library. Look through those and one should have what you need and you can filter the results as desired.

Otherwise if you need more help you'll have to provide more details. Specifically an more detailed example of what you have and what you want. I'm working on guesses, which is usually right, but also very easy to be wrong, especially since your terminology is non-standard.