BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
dougcrites
Fluorite | Level 6

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.

1 ACCEPTED SOLUTION

Accepted Solutions
rogerjdeangelis
Barite | Level 11
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

3 REPLIES 3
Kurt_Bremser
Super User

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.

rogerjdeangelis
Barite | Level 11
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;
ballardw
Super User

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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