Create a dataset of source tables and variables used to create temporary datasets in a SAS program

[Reason15]

The version of SAS that I am using is SAS EG 6.1 M1HF5 (6.100.0.4180) (32-bit)

My question is a simple one, but may not be so simple to answer…

How do I get a dataset that lists the database schema name, table/view name, and the variables used from that table? For example, in the data extract code that follows…I would like to create a dataset that looks similar to the following:

SCHEMA	TABLE/VIEW NAME	VARIABLE NAME
CCW_VIEW_PRD	CASE_SUM	CASE_CLS_DT
CCW_VIEW_PRD	CASE_SUM	ICD_VRSN_CD
CCW_VIEW_PRD	CASE_DTL	PERSN_ELGBTY_ID
CCW_VIEW_PRD	CASE_DTL	CLIENT_ACCT_NUM
CCW_VIEW_PRD	CASE_DTL	CASE_ID
Etc.

I have a program that has the following proc sql for the initial data extract…( there are several similar extracts within the entire program)..SEE EXCERPT BELOW:

proc sql;

connect to teradata ( user = "&user.@LDAP"

                                    password = "&pwd."

                                    server = "&dns."

                                    mode = teradata

                                    schema = CCW_VIEW_PRD  );

Create Table work.csm_dtl As

   Select  

      *

   From Connection To Teradata (

SELECT DISTINCT

A.PERSN_ELGBTY_ID,

A.PERSN_ELGBTY_ID as entpr_cust_id,

A.CUST_ELGBTY_ACCT_KEY,

A.CLIENT_ACCT_NUM,

A.CHNL_CD,

A.CHNL_SRC_CD,

A.CASE_ID,

A.CASE_OPEN_DT,

CASE WHEN CR.DETRMD_ACT_CD = 'DOES_NOT_MEET_CRITERIA' THEN CR.REFRL_DETRMTN_DT  ELSE A.CSM_NOT_ACCPT_DT END AS CSM_NOT_ACCPT_DT,

A.CSM_ACCPT_DT,

CASE WHEN CR.DETRMD_ACT_CD = 'DOES_NOT_MEET_CRITERIA' THEN CR.REFRL_DECLN_RSN_CD  ELSE '' END AS CSM_NOT_ACCPT_RSN_CD,

CASE WHEN CR.DETRMD_ACT_CD = 'DOES_NOT_MEET_CRITERIA' THEN DECLN.CD_SHRT_DESC ELSE '' END AS CSM_NOT_ACCPT_RSN_DESC,

CASE WHEN CR.DETRMD_ACT_CD = 'MEETS_CRITERIA' THEN ACCPT.CD_SHRT_DESC ELSE '' END  AS CSM_ACCPT_RSN_DESC,

CASE WHEN CR.DETRMD_ACT_CD = 'DOES_NOT_MEET_CRITERIA' THEN 'Y'  ELSE '' END AS CM_NOT_ACCEPT,

CASE WHEN CR.DETRMD_ACT_CD = 'MEETS_CRITERIA' THEN 'Y' ELSE '' END  AS CM_ACCEPT,

CASE WHEN CR.DETRMD_ACT_CD = 'MEETS_CRITERIA' OR CR.DETRMD_ACT_CD = 'DOES_NOT_MEET_CRITERIA' THEN 'Y' ELSE '' END  AS CM_REVIEW,

CSU.CASE_CLS_DT AS CSM_DISCHRG_DT,

A.CLSR_RSN_CD AS CSM_DISCHRG_RSN_CD,

DISCH.CD_SHRT_DESC AS CSM_DISCHRG_RSN_DESC,

CCD2.SRC_DESC as req_by_desc,

A.CSM_STAT_CD,

A.CSM_TY_CD,

RCV2.CSM_TY_DESC AS CSM_TY_DESC,

A.CSM_INTSTY_LVL_CTGRY_CD,

CSU.ICD_VRSN_CD,

CSU.PRIM_DIAG_CD,

RCV.CD_SHRT_DESC AS PRIM_DIAG_SHRT_DESC,

C.UM_SAVE_AMT AS TOTL_AUTHZN_SAVE_AMT,

CSV.FNL_SAVEAMT AS TOTL_MED_SAVE_AMT,

DISCH.EFF_DT,

CASE

                        WHEN CSU.ICD_VRSN_CD IN ('09','IC','ICD9') THEN '131-A'

                        WHEN CSU.ICD_VRSN_CD IN ('10','CD10','ICD10') THEN  '897'

END AS CDSET_UNQ_ID_1,

UPPER(COALESCE(CSU.PRIM_DIAG_CD,CSU.SRC_PRIM_DIAG_CD)) AS PRIM_DIAG_CD_DRVD

FROM

CCW_VIEW_PRD.CASE_SUM CSU

INNER JOIN CCW_VIEW_PRD.CASE_DTL A

Etc…

We are creating data crosswalks to a new datasource and a source table and column dataset produced for each reporting program like the one above would be very helpful.

I am hoping that something like this is captured in the metadata when the program is run…

Thanks for any help!

[SuryaKiran]

You can query DBC.Columns/DBC.ColumnsV in pass-through to get the metadata information. Alternatively if there is a Libname assigned you can use the Dictionary tables or SAS Views for the metadata information. I recommend using pass-through, it might give results much faster. 

proc sql;
connect to teradata ( user = "&user.@LDAP" password = "&pwd." server = "&dns." );
create table Schema_Info as 
select * 
	from connection to teradata 
	(
SELECT t.DatabaseName, t.TableName, t.TableKind, ColumnName
FROM dbc.TablesV AS t JOIN dbc.ColumnsV AS c
  ON t.DatabaseName = c.DatabaseName
 AND t.TableName = c.TableName 
WHERE t.DatabaseName='CCW_VIEW_PRD'
AND TableKind in ('T','V')  /* Table/Views Only */
);
disconnect from teradata;
quit;

Alternatively,

libname TD_LIB teradata user = "&user.@LDAP" password = "&pwd." server = "&dns." schema=CCW_VIEW_PRD;

proc sql;
create table Schema_Info as 
select t.sysvalue as Schema_Name,c.memname as table_name,c.name as Column_name 
from sashelp.vcolumn as c
left join sashelp.vlibnam as t
on t.libname=c.libname
where c.libname="TD_LIB";
quit;