- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Posted 10-30-2015 01:32 PM
(1264 views)
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
The just apply the result of the comparison to your original table using SQL update or data step modify.
Data never sleeps
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Hi Ballardw, I appreciate your detailed solution! I will try implement it and let you know how it works.
Thanks, Zhuo
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Hi Ballardw,
I have a chance and implemented the solution. It worked perfectly! You are awesome!
Zhuo