topic Re: SAS Table Update, dependent of test result in SAS Programming

SAS Table Update, dependent of test result

zz — Fri, 30 Oct 2015 17:32:49 GMT

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!

Re: SAS Table Update, dependent of test result

LinusH — Fri, 30 Oct 2015 17:39:00 GMT

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.

Re: SAS Table Update, dependent of test result

ballardw — Fri, 30 Oct 2015 18:06:39 GMT

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.

Re: SAS Table Update, dependent of test result

zz — Fri, 30 Oct 2015 19:05:30 GMT

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

Thanks, Zhuo

Re: SAS Table Update, dependent of test result

zz — Mon, 02 Nov 2015 14:16:43 GMT

Hi Ballardw,

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

Zhuo