topic Re: How do I programatically detrmine the data type of a particular column in multiple tables? in SAS Procedures

How do I programatically detrmine the data type of a particular column in multiple tables?

Mike7085 — Wed, 05 Sep 2012 19:45:53 GMT

I have a program that compiles results from other individual programs (that verify that tables contain valid data - source to target). Occasionally, something happens where an individual program that creates the results table abends or creates an empty dataset. When this happens, the result table ends up with columns that have a data type of numeric rather than char (normal). When this happens, the program that compiles all the results chokes because the data types of table columns (numeric) of empty datasets don’t agree with the ‘good’ ones (char).

I can run proc datasets to get the column data types of the results datasets and go through them visually to determine which datasets are bad but the number of datasets can exceed 100 so this is a labor intensive effort. My question is: Is there a way (progamatically) to identify result datasets where the data type of a particular column is numeric?

Re: How do I programatically detrmine the data type of a particular column in multiple tables?

Reeza — Wed, 05 Sep 2012 19:54:16 GMT

Try looking at the dictionary tables, this paper is a good read on the topic.

You could also save the contents of proc datasets to a table and work with that, but I think the dictionary tables are a better way to go.

www2.sas.com/proceedings/sugi30/070-30.pdf

Re: How do I programatically detrmine the data type of a particular column in multiple tables?

Linlin — Wed, 05 Sep 2012 20:10:39 GMT

is the example helpful?

proc contents data=sashelp.class

out=temp(keep=libname memname type name where=(lowcase(name)='age')) noprint;

proc print;run;

obs LIBNAME MEMNAME NAME TYPE

1 SASHELP CLASS Age 1

Re: How do I programatically detrmine the data type of a particular column in multiple tables?

Mike7085 — Wed, 05 Sep 2012 22:25:11 GMT

Between the two answers, I got what I needed. Here's the code I ran:

libname fdrcert '/depts/results';

proc sql;

select name, memname, type

from dictionary.columns

where upcase(libname) = 'FDRCERTt' and

upcase(name) = 'TARGET';

quit;

Thanks to you both for your quick and helpful responses!!:smileygrin:

Re: How do I programatically detrmine the data type of a particular column in multiple tables?

Tom — Wed, 05 Sep 2012 23:18:19 GMT

You actually do not want to code upcase(LIBNAME). The values of the LIBNAME variable in DICTIONARY.COLUMNS will always be in uppercase. If you include the UPCASE() function then SAS cannot use its internal index to skip looking at the datasets in all of the other librefs that you might have defined. This can slow the execution down a lot.

Note also that an extra lowercase t seems to have slipped into the code you posted.