DATA Step, Macro, Functions and more

Proc sql describe on VTABLE observations

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 8
Accepted Solution

Proc sql describe on VTABLE observations

Hi, I would like to be able to loop thru the SASHELP.VTABLE  and do a PROC SQL DESCRIBE for each table in VTABLE.  I would like to be able to insert text in between the describe ouput and write all to an output dataset.

 

So, like:

 

table 'mylib.table1'

DESCRIBE output....  CREATE....

table 'mylib.table2'

DESCRIBE output....  CREATE....

 

So far, I haven't been able to achieve this.

 

Thanks for any help.


Accepted Solutions
Solution
‎03-27-2017 02:07 PM
Valued Guide
Posts: 505

Re: Proc sql describe on VTABLE observations

Describe and copy all tables in dictionary.vstable (except MAP daatsets)

inspired by
https://goo.gl/s4N3NG
https://communities.sas.com/t5/Base-SAS-Programming/Proc-sql-describe-on-VTABLE-observations/m-p/343222

Issues with code
 1.  Only does 5 tables, but just uncomment do loop
 2. Only copies 10 obs (just remove obs=10)

Proc descibe outputs to the log. You will need to use altlog or
proc printto log= to save to text file. However I suggest you use ods
and proc contents.


You don't have to use the DOSUBL. You can make this
several separate SQLs and datasteps.

I am not dond of 'call execute', which you can also use.
Note you have control after is describe so you can test
return codes or internediate results.

HAVE  SASHELP.VSTABLE with 182 tables
======================================

Up to 40 obs SASHELP.VSTABLE total obs=182

Obs    LIBNAME    MEMNAME

  1    SASHELP    AACOMP
  2    SASHELP    AARFM
  3    SASHELP    ADSMSG
  4    SASHELP    AFMSG
  5    SASHELP    AIR

 180   SASUSER    SASMBC
 181   SASUSER    SASPARM
 182   WORK       AACOMP

WANT
======

Describe for every table ( in the log)

create table SASHELP.AACOMP( compress=CHAR  bufsize=65536 )
  (
   LOCALE char(5),
   KEY char(60),
   LINENO num,
   TEXT char(1200)
  );
create index INDX on SASHELP.AACOMP(LOCALE,KEY);

And work dataset fro each table of 10 obs

Up to 40 obs from WORK.AACOMP total obs=10

Obs    LOCALE    KEY                                   LINENO    TEXT

  1      en      MODEL_ASE_VLABEL                         1      Average Squared Error
  2      en      MODEL_CODEDEPENDENCY_ERROR               1      Unable to determine the code variable dependencies.
  3      en      MODEL_CORRECTRATE_VLABEL                 1      Overall Precision Rate
  4      en      MODEL_CRCUT_VLABEL                       1      CR Cutoff
  5      en      MODEL_CRDEPTH_VLABEL                     1      CR Sample Depth
  6      en      MODEL_CREATEINTERMEDIATEFILE_ERROR       1      Intermediate file creation failed.
  7      en      MODEL_CR_VLABEL                          1      Maximum Classification Rate (CR)
  8      en      MODEL_CUMEVENTCOUNT_VLABEL               1      Cumulative Event Count
  9      en      MODEL_CUMLIFT_VLABEL                     1      Cumulative Lift
 10      en      MODEL_CUMNOBS_VLABEL                     1      Cumulative Number of Observations


WORKING CIODE

    DOSUBL
      select
         quote(trim(memname))
        ,quote(trim(libname))
      into
         :mems separated by ","
        ,:libs separated by ","
      from
         sashelp.vstable

      do i=1 to dim(mems)

   DOSUBL
         describe table &lib..&mem
      data work.&mem;
         set &lib..&mem(obs=10);

      end;

FULL SOLUTION
=============

 %let prt=d:/txt/describe.txt;
 %utlfkil(&prt);

 %symdel mem lib mems libs/ nowarn;
 proc datasets lib=work kill nolist;
 run;quit;

 data _null_;

  if _n_=0 then do;
    %let rc=%sysfunc(dosubl('
       proc sql noprint;
        select
           quote(trim(memname))
          ,quote(trim(libname))
        into
           :mems separated by ","
          ,:libs separated by ","
        from
           sashelp.vstable
        where
           not (libname eqt "MAP")
       ;quit;
    '));
   end;

   array mems[&sqlobs] $32 (&mems);
   array libs[&sqlobs] $8  (&libs);
   do i=1 to 5 /* dim(mems) */ ;
     call symputx('mem',mems[i]);
     call symputx('lib',libs[i]);
     rc=dosubl('
        title "&mem";
        proc sql;
           describe table &lib..&mem
        ;quit;
        data work.&mem;
           set &lib..&mem(obs=10);
        run;quit;
     ');
   end;

run;quit;

View solution in original post


All Replies
Super User
Posts: 6,936

Re: Proc sql describe on VTABLE observations

If you want table metadata in a dataset, why not use sashelp.vcolumn or dictionary.columns (in SQL) directly?

Or use proc contents with an output dataset.

This can then be automated by running it with call execute from sashelp.vtable.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Solution
‎03-27-2017 02:07 PM
Valued Guide
Posts: 505

Re: Proc sql describe on VTABLE observations

Describe and copy all tables in dictionary.vstable (except MAP daatsets)

inspired by
https://goo.gl/s4N3NG
https://communities.sas.com/t5/Base-SAS-Programming/Proc-sql-describe-on-VTABLE-observations/m-p/343222

Issues with code
 1.  Only does 5 tables, but just uncomment do loop
 2. Only copies 10 obs (just remove obs=10)

Proc descibe outputs to the log. You will need to use altlog or
proc printto log= to save to text file. However I suggest you use ods
and proc contents.


You don't have to use the DOSUBL. You can make this
several separate SQLs and datasteps.

I am not dond of 'call execute', which you can also use.
Note you have control after is describe so you can test
return codes or internediate results.

HAVE  SASHELP.VSTABLE with 182 tables
======================================

Up to 40 obs SASHELP.VSTABLE total obs=182

Obs    LIBNAME    MEMNAME

  1    SASHELP    AACOMP
  2    SASHELP    AARFM
  3    SASHELP    ADSMSG
  4    SASHELP    AFMSG
  5    SASHELP    AIR

 180   SASUSER    SASMBC
 181   SASUSER    SASPARM
 182   WORK       AACOMP

WANT
======

Describe for every table ( in the log)

create table SASHELP.AACOMP( compress=CHAR  bufsize=65536 )
  (
   LOCALE char(5),
   KEY char(60),
   LINENO num,
   TEXT char(1200)
  );
create index INDX on SASHELP.AACOMP(LOCALE,KEY);

And work dataset fro each table of 10 obs

Up to 40 obs from WORK.AACOMP total obs=10

Obs    LOCALE    KEY                                   LINENO    TEXT

  1      en      MODEL_ASE_VLABEL                         1      Average Squared Error
  2      en      MODEL_CODEDEPENDENCY_ERROR               1      Unable to determine the code variable dependencies.
  3      en      MODEL_CORRECTRATE_VLABEL                 1      Overall Precision Rate
  4      en      MODEL_CRCUT_VLABEL                       1      CR Cutoff
  5      en      MODEL_CRDEPTH_VLABEL                     1      CR Sample Depth
  6      en      MODEL_CREATEINTERMEDIATEFILE_ERROR       1      Intermediate file creation failed.
  7      en      MODEL_CR_VLABEL                          1      Maximum Classification Rate (CR)
  8      en      MODEL_CUMEVENTCOUNT_VLABEL               1      Cumulative Event Count
  9      en      MODEL_CUMLIFT_VLABEL                     1      Cumulative Lift
 10      en      MODEL_CUMNOBS_VLABEL                     1      Cumulative Number of Observations


WORKING CIODE

    DOSUBL
      select
         quote(trim(memname))
        ,quote(trim(libname))
      into
         :mems separated by ","
        ,:libs separated by ","
      from
         sashelp.vstable

      do i=1 to dim(mems)

   DOSUBL
         describe table &lib..&mem
      data work.&mem;
         set &lib..&mem(obs=10);

      end;

FULL SOLUTION
=============

 %let prt=d:/txt/describe.txt;
 %utlfkil(&prt);

 %symdel mem lib mems libs/ nowarn;
 proc datasets lib=work kill nolist;
 run;quit;

 data _null_;

  if _n_=0 then do;
    %let rc=%sysfunc(dosubl('
       proc sql noprint;
        select
           quote(trim(memname))
          ,quote(trim(libname))
        into
           :mems separated by ","
          ,:libs separated by ","
        from
           sashelp.vstable
        where
           not (libname eqt "MAP")
       ;quit;
    '));
   end;

   array mems[&sqlobs] $32 (&mems);
   array libs[&sqlobs] $8  (&libs);
   do i=1 to 5 /* dim(mems) */ ;
     call symputx('mem',mems[i]);
     call symputx('lib',libs[i]);
     rc=dosubl('
        title "&mem";
        proc sql;
           describe table &lib..&mem
        ;quit;
        data work.&mem;
           set &lib..&mem(obs=10);
        run;quit;
     ');
   end;

run;quit;
Super User
Posts: 10,497

Re: Proc sql describe on VTABLE observations

You don't show anything about what kind of text you want to "insert" between the describe output.

 

 

I would very strongly recommend you start with something like:

proc sql;
   create table want as
   select * 
   from  dictionary.tables
         where Libname='MYLIB';
quit;

to get the data set.

 

Then manipulate that data such as with BY group processing using the MEMNAME variable.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 178 views
  • 0 likes
  • 4 in conversation