SAS Check dynamically if a libname/table/column EXISTS and update a field on that table

Question

I have a table with library, table, column and a result flag.

library	table	columnA	columnB	result_flg
lib1	tab1	field1
lib1	tab2		field4
lib3	tab3	field3	field5

If the field columns (A or B) not missing, then the goal its to check if that columns exist in that lib/table dynamically and update the result_flag field with true or false.

Whats the efficient approach?

Tks in advance.

Answer 1

You can use an in-line SQL query to check if the column exists and make it a 1 if it exists and 0 if it does not. The shortcut (boolean condition) will automatically create this. You can use the update statement to update the table in place.

data have;
    input library$ table$ column$;
    datalines;
sashelp air air
sashelp air notexist
sashelp class .
sashelp class name
;
run;

proc sql;
     update have
         set result_flg = (upcase(column) IN 
                            (select upcase(name)
                             from dictionary.columns 
                             where    upcase(library) = upcase(libname) 
                                  AND upcase(table)   = upcase(memname)
                            )
                          )
         where NOT missing(column)
     ;
quit;