Hi,
Example, if I have a library "c:\example", that has 10 different datasets. I want to look for a variable ID with the value of 5. Is there a way to do this?
Thanks
For small datasets, you can use call execute(), the data-driven programming skill.
proc datasets lib=work nolist nowarn;
delete final_out;
quit;
libname search "c:\example";
data _null_;
set sashelp.vcolumn;
where libname='SEARCH' and memtype='DATA' and upcase(name)='ID';
by memname notsorted;
if first.memname then call execute('
data temp_out;
set '||catx('.',libname,memname)||' indsname=indsname;
');
call execute('
_dsname_=indsname;
_row_=_n_;
_raw_value_=cats('||trim(name)||');
_fmt_value_=strip(vvalue('||trim(name)||'));
if _raw_value_="5";
');
if last.memname then call execute('
keep _dsname_ _row_ _raw_value_ _fmt_value_;
run;
proc append data=temp_out base=final_out force;
run;
');
run;
What can you tell us about the data? Is there a guarantee that ID exists in every data set? Is there a guarantee that it is defined the same way (length, numeric vs. character) in every data set?
Sorry I wasn't been clear.
No. I just need to go through a list of datasets and check if the dataset has the variable ID and it does, then retrieve the rows where ID = 5. I wasn't sure if this was possible with SAS.
you could maybe write a macro to do this.
For small datasets, you can use call execute(), the data-driven programming skill.
proc datasets lib=work nolist nowarn;
delete final_out;
quit;
libname search "c:\example";
data _null_;
set sashelp.vcolumn;
where libname='SEARCH' and memtype='DATA' and upcase(name)='ID';
by memname notsorted;
if first.memname then call execute('
data temp_out;
set '||catx('.',libname,memname)||' indsname=indsname;
');
call execute('
_dsname_=indsname;
_row_=_n_;
_raw_value_=cats('||trim(name)||');
_fmt_value_=strip(vvalue('||trim(name)||'));
if _raw_value_="5";
');
if last.memname then call execute('
keep _dsname_ _row_ _raw_value_ _fmt_value_;
run;
proc append data=temp_out base=final_out force;
run;
');
run;
What do you expect as result?
Assuming that the variable "id" exists in every dataset in libname example and it is always numeric, you could write this for one dataset:
proc sql;
select *
from example.first
where Id = 5;
quit;
The datasets existing in one library can be retrieved from sashelp.vtable:
proc sql noprint;
create table work.tables as
select cats(LibName, '.', MemName) as Dataset
from sashelp.vtable
where LibName = 'EXAMPLE';
quit;
The result is used to create a select-statement for each table:
data _null_;
set work.tables end=jobDone;
if _n_ = 1 then do;
call execute('proc sql;');
end;
call execute(catx(' ', 'select * from', Dataset, 'where Id = 5;'));
if jobDone then do;
call execute('quit;');
end;
run;
Code is untested!
I learned call execute in a class I took at my university.
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.
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.