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.
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;
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.
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;
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.