Help using Base SAS procedures

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 10
Accepted Solution

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

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?


Accepted Solutions
Solution
‎09-05-2012 04:10 PM
Super Contributor
Posts: 1,636

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

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

View solution in original post


All Replies
Super User
Posts: 17,819

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

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

Solution
‎09-05-2012 04:10 PM
Super Contributor
Posts: 1,636

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

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

Occasional Contributor
Posts: 10

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

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:

Super User
Super User
Posts: 6,500

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

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.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 172 views
  • 0 likes
  • 4 in conversation