topic Re: Is there a way to search for a value of a variable in multiple data sets? in New SAS User

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

cosmid — Fri, 29 Dec 2023 16:40:05 GMT

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

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

Astounding — Fri, 29 Dec 2023 17:31:52 GMT

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?

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

tarheel13 — Mon, 01 Jan 2024 17:20:33 GMT

you could maybe write a macro to do this.

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

whymath — Tue, 02 Jan 2024 03:33:09 GMT

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;

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

andreas_lds — Thu, 04 Jan 2024 12:17:38 GMT

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!

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

cosmid — Fri, 05 Jan 2024 02:41:31 GMT

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.

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

cosmid — Fri, 05 Jan 2024 02:47:30 GMT

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!

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

cosmid — Fri, 05 Jan 2024 02:50:10 GMT

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!

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

tarheel13 — Fri, 05 Jan 2024 12:17:48 GMT

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