Writing a Macro that checks if a table qualifies

[deleted_user]

I need SAS Code that does the following:
1. Checks if selected columns are in fact in the Data Table
2. Checks if above columns are in certain formats
3. Checks if the data in the above columns are usable
4. If the table fails any of these tests, an detailed error should be given and the process must be stopped.

I'm adding this code to "Idiot Proof" our current SAS Macros. I have no idea where to start...

The only one that I know how to do is point 3.

[Patrick]

For point 1 and 2: Query the dictionary tables (or the corresponding views in SASHELP). The dictionary table is either 'tables' or 'columns' - never sure about this.

[LinusH]

Another possibility is to use data step functions to query the table directly.
For point 4 I guess you are into some extensive if-then logic, where you combine the type of error (point 1-3) with the information retrieved by point 3 together with original information you got (table/column name etc).

/Linus

[deleted_user]

Suppose 'Clnmsg' is the table you want to query.
Use proc contents to create a table containing structure of 'clnmsg' dataset.
you can use the keep option to keep only the required columns.

proc contents data = clnmsg out = structure(keep = memname name type length format formatl formatd informat informl informd);
run;

you can now freely query the 'structure' dataset to check the datatypes, formats etc.

[Cynthia_sas]

Hi:
The dictionary tables already exist. You don't necessarily need to create an output data set from PROC CONTENTS. If you search previous forum postings for "dictionary.tables" and "dictionary.columns", you should find some other examples.

cynthia
[pre]
proc sql;
create table structure as
select libname, memname, name, type, length,
format, informat, label
from dictionary.columns
where libname = 'SASHELP' and
memname = 'SHOES';
run;
quit;

proc print data=work.structure;
run;
[/pre]

[deleted_user]

i forgot to mention,

memname : contains tablename
name : contains variablename
type : contains variable type (1 for numeric, 2 for character)
length : contains length
format : contains formatname
formatl : contains format length
formatd : contains number of digits post decimal