EZ Study

Actuarial Biology Chemistry Economics Calculators Confucius Engineer

"Smart" application of SAS library dictionaries
One click: Output all SAS files in a folder into csv files

Question: In SAS data modeling practice, sometimes we need to output all sas files
in some folder to csv or excel readable files. How can we do this automatically?

Answer: Most of us didn't realize how powerful of SAS library dictionaries. We can make some really cool stuff if we use them wisely. The following code reads all sas files from some folder into csv automatically.

        %let dir=D:\test; /*change to the folder that contains SAS files;

        /*********the codes below needn't be changed*********/
        options nofmterr nonotes;
        libname mydb "&dir";
        x "cd &dir";
        %let library = mydb;

        title "select all sas datasets names from a folder";
        proc sql;
        create table all_table as select memname, memtype from dictionary.members

        where libname=upcase("&library") and memtype="DATA";

        data _null_;
        set all_table end=last;
        if last then call symput('nobs',trim(_n_));

        %macro transfer(nobs);

        %let number=1;

        %do %while(&number le &nobs);

        proc printto new log='log.log' print='output.log';

        data _null_; set all_table;
        if _n_=&number
        then call symput('dsname',trim(memname));

        ods select Contents.DataSet.Variables;
        ods output Contents.DataSet.Variables=&dsname._desc(drop=Member Num);

        proc contents data=&LIBRARY..&dsname;
        ods output Close;

        proc sort;
        by pos; run;

        data &dsname._vars;
        format num 5.;
        set =&dsname._desc;
        num=_n_; drop pos;         run;

        proc export data=&LIBRARY..&dsname
        outfile="&dsname..csv" dbms=csv replace;

        proc export data=&dsname._vars
        outfile="&dsname._vars.txt" dbms=csv replace;

        %let number=%eval(&number+1);


        proc printto; run;

To have more fun, play more in the SAS library dictionaries, go to the following tutorial for a few more exmaples.

        title "Only the tables's name from the Dictionary libref";
        proc sql;
        create table dictionarymembers as
        select unique memname from dictionary.dictionaries;

        /* 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");

Related links:
Continue to: How to send email in SAS part-I   SAS tutorial home
Back to: Automation for Data Exploring in data mining   Statistics tutorial home