BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Mike7085
Calcite | Level 5

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Linlin
Lapis Lazuli | Level 10

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

4 REPLIES 4
Reeza
Super User

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

Linlin
Lapis Lazuli | Level 10

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

Mike7085
Calcite | Level 5

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:

Tom
Super User Tom
Super User

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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