SAS Table Update, dependent of test result

[zz]

I have a SAS table with columns of dataset name and field names.  I need to take the dataset name, and field name in each row and test if the dataset exists under a directory; if exists, I need to test if the field exists in the dataset.  Then I need to update the table with two additional columns DatasetExit and FieldExist with 'Yes' and 'No'.

Any suggestions would be highly apprecaited!

[LinusH]

You could use proc contents, proc datasets or the views vs table and column in sashelp to get the actual table attributes. There is also a bunch of Sas file functions at hand if you prefer.
The just apply the result of the comparison to your original table using SQL update or data step modify.

[ballardw]

Something like this perhaps?

 data have;
   informat memname name $32.;
   input memname name;
   label memname='Data set name'
         name   ='Variable name'
   ;
datalines;
class sex
class name
class town
iris species
iris petalwidth
iris weight
frivolous name
;
run;


proc sql;
   create table want as
   select a.*, exist(cats("SASHELP.",a.memname),'DATA') as DataSetExists, 
              (not missing(b.name) ) as FieldExist
   from have as a left join ( select * from dictionary.columns where libname='SASHELP') as b on
      upcase(a.memname)=upcase(b.memname) and upcase (a.name)=upcase(b.name);
quit;
       
     

This has 1 for yes and 0 for no. You can assign a format or add logic. I recommend a format.

[zz]

Hi Ballardw, I appreciate your detailed solution!  I will try implement it and let you know how it works.

Thanks, Zhuo

[zz]

Hi Ballardw,

I have a chance and implemented the solution.  It worked perfectly!  You are awesome!

Zhuo