data profile macro

deleted_user — Fri, 04 Sep 2009 22:25:16 GMT

I have 100 oracle tables that I can access through SAS and I want to create a dataset for reporting purposes that contains the count of records, min, max for every column in the the table.

So if Oracle has a table with the following:

table xyz
name1 name 2 name 3
abc def hij
bca lmn

The sas dataset would have the following information
tablename columnname count min max
xyz name1 2 abc bca
xyz name2 1 def
xyz name3 2 hij lmn

I have all the tables columns and fieldnames in a datset already from a proc dataset. I am just not sure how to iterate through each column and set of a proc sql against the database to return the count min and max.

I have some code that comes close but not quite what I am trying to achieve.

Re: data profile macro

sbb — Sat, 05 Sep 2009 00:23:30 GMT

Share the code you already have for feedback. Also, if you have the PROC DATASETS output then you also have access to the SAS view of the LIBNAME associated with the SAS datasets -- you can use the SAS DICTIONARY TABLES view facility to build a SAS dataset list of each table and the associated colums/variables in each table. Then with this information you can use SAS to generate PROC MEANS to get the MIN / MAX variable values within each SAS table.

Recommend searching the SAS support http://support.sas.com/ website for DICTIONARY TABLES views using PROC SQL (or the DATA step alternative being SASHELP.Vxxxxxxx members, such as SASHELP.VCOLUMN.

And you can either use SAS DATA step logic to generate the PROC MEANS code for each table, and capture the MEANS output to generate your desired info listed in your post.

The SAS support website not only as the SAS-hosted product documentation guides but also there are technical and conference topic-oriented papers that will help guide you.

Scott Barry
SBBWorks, Inc.

topic data profile macro in SAS Programming

data profile macro

Re: data profile macro