SAS Programming

DATA Step, Macro, Functions and more
BookmarkSubscribeRSS Feed
zz
Calcite | Level 5 zz
Calcite | Level 5

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!

4 REPLIES 4
LinusH
Tourmaline | Level 20
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
ballardw
Super User

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
Calcite | Level 5 zz
Calcite | Level 5

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

 

Thanks, Zhuo

zz
Calcite | Level 5 zz
Calcite | Level 5

Hi Ballardw,

 

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

 

Zhuo

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 1266 views
  • 0 likes
  • 3 in conversation