DATA Step, Macro, Functions and more

SAS Table Update, dependent of test result

Reply
Contributor zz
Contributor
Posts: 28

SAS Table Update, dependent of test result

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!

Super User
Posts: 5,429

Re: SAS Table Update, dependent of test result

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.
Data never sleeps
Super User
Posts: 11,343

Re: SAS Table Update, dependent of test result

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.

 

Contributor zz
Contributor
Posts: 28

Re: SAS Table Update, dependent of test result

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

 

Thanks, Zhuo

Contributor zz
Contributor
Posts: 28

Re: SAS Table Update, dependent of test result

Hi Ballardw,

 

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

 

Zhuo

Ask a Question
Discussion stats
  • 4 replies
  • 196 views
  • 0 likes
  • 3 in conversation