EZ Study

Actuarial Biology Chemistry Economics Calculators Confucius Engineer
Physics
C.S.

Cool Tips & Tricks of Proc SQL - 2
Play in the SAS library dictornaries
Find the column information from metadata

You may never realize how useful of using SAS library dictornaries. You will get much more fun if you play a little bit more there. Here are a few exmaples.

• Find the column information from metadata
SAS stores the metadata at its DICTIONARY datasets(very popular SAS paper).
PROC SQL can visit the column information easily, without using CONTENTS proc.
proc sql;
   select name into: vars.
   separated by ' '
   from sashelp.vcolumn
   where lowcase(libname) = 'work' and lowcase(memname) = 'class'
   and lowcase(type)='num';
quit;
        title "Only the tables's name from the Dictionary libref";
        proc sql;
        create table dictionarymembers as
        select unique memname
        from dictionary.dictionaries; quit;

        /* get all tables' info(libname memname memtype path etc.) */
        proc sql; describe table dictionary.members; quit; /* Output in log ;


        title "Output All data names from a specific library";
        proc sql;
        select memname from dictionary.members
        where libname=upcase("sasuser") and memtype=upcase("data");
        quit;

        title "Ouptut all tables' name into a macro variable";
        proc sql;
        select memname into: dsname separated by ' '
        from dictionary.members
        where libname = upcase("sasuser") and memtype=upcase("data");
        %put &dsname;
        quit;

        /* getcolumns' info(name format informat length label etc.) */
        proc sql;
        describe table dictionary.columns; quit;

        title "Ouptut all variables' name Beginning with M";
        proc sql;
        select name into :varname separated by ' ' from DICTIONARY.COLUMNS
        where libname = "SASHELP" and memname = "CARS"
        and substr(strip(name),1,2) like "M%" ;
        /*drop those variables from the dataset*/
        data data1; set SASHELP.CARS(drop=&varname); run;

        title "output all variables freq with format";
        proc sql;
        select strip(name), strip(name)||' '||format
        into: varname separated by ' ', : varfmt separated by ' '
        from DICTIONARY.COLUMNS
        where libname = "SASHELP" and memname = "CARS" and format ^= ' ';
        quit;

        %put ***VARNAME: &varname ***VARFMT: &varfmt;
        proc freq data=sashelp.cars; tables &varname./missing;
        format &varfmt; run;
        proc freq data=sashelp.cars; table EngineSize *Cylinders;
        format EngineSize 8.0; run;

        title "Output # of obs and # of variables";
        proc sql;
        select strip(put(nobs,8.)), strip(put(nvar,8.))
        into :nobs, :nvar from DICTIONARY.TABLES
        where libname = "SASHELP" and memname = "CARS"; quit;
        %put ***nobs: &nobs ***nvar: &nvar;

        title "output all data created after 2011";
        proc sql;
        describe table dictionary.tables;
        select MEMNAME into :valdata separated by ' ' from DICTIONARY.TABLES
        where libname = "SASUSER" and memtype="DATA"
        and year(datepart(CRDATE)) gt 2011; quit;
        %put &valdata;

        title "output table names with path";
        proc sql;
        select strip(memname)||' '||strip(path) from dictionary.members
        where libname = upcase("sasuser") and memtype=upcase("data");
        quit;

        title "get the size of SAS dataset";
        proc sql;
        create table tab as
        select libname, memname, nobs, obslen,
        nobs*obslen/(1024*1024) as Mega_Bytes
        from dictionary.tables
        where libname='YOURLIB'; quit;


Related links:
Continue to: SAS Cool Tips & Tricks: Moving Average, Running Total   SAS tutorial home
Back to: SAS Cool Tips & Tricks of Proc SQL; How to Rank a variable ?   Statistics tutorial home