Is there a way to search for a value of a variable in multiple data sets?

[cosmid]

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

[Astounding]

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?

[cosmid]

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.

[tarheel13]

you could maybe write a macro to do this. 

[whymath · Accepted solution]

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;

[cosmid]

Why it only works on small datasets? I didn't know about sashelp.vcolumn and the call execute. I wlll do more research to fully understand the code. Thank you!

[andreas_lds]

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!

[cosmid]

Thanks for breaking up the code into sections. It is much easier to see how they work that way. I will do more research to learn about the sashelp.vtable and call execute.
Where did you guys learn the call execute from? Is there another SAS certification other than Base and Advanced? Thanks!

[tarheel13]

I learned call execute in a class I took at my university.