SAS Data Dictionary

SAS Data DictionaryRegina SAS User Group – 2017-09-20Patrice BourdagesCGI SAS Senior Consultant CGI Group Inc. CONFIDENTIAL

Agenda Intended targetObjectivesData SourceSAS EnvironmentWhat are SAS DD tablesSAS Dictionary Tables (27)Reference sheetReal life exampleDemo (if available)ConclusionReferencesQuestion Period

Intended target SAS programmerSAS AnalystActuariesDBA (Data Base Administrator)StatisticianEtc Anyone who uses SAS !!!!3

Objectives Learn about the existence of the SAS Data Dictionary (SAS DD) Learn how to extract information from the SAS DD Demonstrate a real life example4

Where does our data come from? SAS Tables External Databases SQL Server, Oracle, MySQL, Sybase, etc Worksheets Excel, Lotus, etc. ASCII flat files .txt, .csv5

SAS Work environment (Example)6

What are SAS DD tables?A DICTIONARY table is a : Read-only SAS view that contains information about SAS libraries,SAS data sets, SAS macros, and external files that are in use oravailable in the current SAS session. Also contains the settings for SAS system options that are currently ineffect.When you access a DICTIONARY table SAS determines the current state of the SAS session and returns thedesired information accordingly. This process is performed each timea DICTIONARY table is accessed, so that you always have currentinformation.7

How do we access these tables?DICTIONARY tables can be accessed by a SAS program by using eitherof these methods: run a PROC SQL query against the table, using the DICTIONARYlibref use any SAS procedure or the DATA step, referring to the PROC SQLview of the table in the Sashelp library8

Dictionary Table List (Page 1 of 6)DICTIONARY TableSashelpViewDescriptionCATALOGSVcatalgContains information about known SAScatalogs.CHECK CONSTRAINTS VchkconContains information about knowncheck constraints.COLUMNSContains information about columns inall known tables.VcolumnCONSTRAINT COLUMNVcncoluUSAGEContains information about columns thatare referred to by integrity constraints.CONSTRAINT TABLEUSAGEContains information about tables thathave integrity constraints defined onthem.Vcntabu9

Dictionary Table List (Page 2 of 6)DICTIONARY ATAITEMSVdataitContains information about knowninformation map data items.DESTINATIONSVdestContains information about known ODSdestinations.DICTIONARIESVdctnryContains information about allDICTIONARY tables.ENGINESVengineContains information about SASengines.EXTFILESVextflContains information about knownexternal files.FILTERSVfilterContains information about knowninformation map filters.10

Dictionary Table List (Page 3 of 6)DICTIONARY ontains information about currentlyaccessible formats and informats.FUNCTIONSVfuncContains information about currentlyaccessible functions.GOPTIONSVgoptValloptContains information about currentlydefined graphics options ( SAS/GRAPHsoftware). Sashelp.Vallopt includes SASsystem options as well as graphicsoptions.INDEXESVindexContains information about knownindexes.INFOMAPSVinfompContains information about knowninformation maps.11

Dictionary Table List (Page 4 of 6)DICTIONARY TableSashelpViewDescriptionLIBNAMESVlibnamContains information about currentlydefined SAS libraries.MACROSVmacroContains information about currentlydefined macro VstabvwVsviewContains information about all objectsthat are in currently defined SASlibraries. Sashelp.Vmember containsinformation for all member types; theother Sashelp views are specific toparticular member types (such as tablesor views).VoptionValloptContains information about SAS systemoptions. Sashelp.Vallopt includesgraphics options as well as SAS systemoptions.OPTIONS12

Dictionary Table List (Page 5 of 6)DICTIONARY TableSashelpViewDescriptionREFERENTIAL CONSTRAINTSVrefconContains information about referentialconstraints.REMEMBERVremembContains information about knownremembers.STYLESVstyleContains information about known ODSstyles.TABLE CONSTRAINTSVtabconContains information about integrityconstraints in all known tables.TABLESVtableContains information about knowntables.TITLESVtitleContains information about currentlydefined titles and footnotes.13

Dictionary Table List (Page 6 of 6)DICTIONARY TableSashelpViewDescriptionVIEWSVviewContains information about known dataviews.VIEW SOURCESNotavailableContains a list of tables (or other views)referenced by the SQL or DATASTEPview, and a count of the number ofreferences.XATTRSVxattrContains information about extendedattributes.14

Dictionary Tables Reference Card15

Real life example Search A variable A label A Table List current SAS Session parameters Etc Automatization Create loading program Validate source table structure vs actual (live) one Check if change in structure, variable name, variable format, etc.16

How to: Available SAS Libraries** In order to know available libraries **;proc sql;create table work.dict librairies asselect *from dictionary.libnames;quit;17

How to: Available SAS Datasets (Tables)** In order to find out available tables **;proc sql;create table work.dict tables asselect *from dictionary.tables;quit;18

How to: Table columns (SAS Datasets variable)** List all the columns of a table / Variables of a SAS Dataset**;proc sql;create table work.colonnes asselect *from dictionary.columns;quit;19

How to: Defined Formats** Produces the list of all available defined formats in your current SASSession (work) **;proc sql;create table work.d formats asselect *from dictionary.formatswhere libname "WORK";quit;20

DemoPsst Switch screen 21

Conclusion SAS Data Dictionary Tables Your version of Webster for all youravailable data Will it be useful to you? I am convinced it will and sooner then youthink. (Now that you know) 22

References Accessing SAS System Information by Using DICTIONARYTables c/62086/HTML/default/viewer.htm#a001385596.htm Summary of SAS Dictionary Tables and Views s.pdf sRefCard.pdf23

Question period !!!24

Our commitment to youWe approach every engagement with oneobjective in mind: to help clients succeed

DICTIONARIES Vdctnry Contains information about all DICTIONARY tables. ENGINES Vengine Contains information about SAS engines. EXTFILES Vextfl Contains information about known external files. FILTERS Vfilter Contains information about known information map filters.