BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
cosmid
Lapis Lazuli | Level 10

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

1 ACCEPTED SOLUTION

Accepted Solutions
whymath
Lapis Lazuli | Level 10

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;

View solution in original post

8 REPLIES 8
Astounding
PROC Star

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
Lapis Lazuli | Level 10

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
Rhodochrosite | Level 12

you could maybe write a macro to do this. 

whymath
Lapis Lazuli | Level 10

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
Lapis Lazuli | Level 10
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
Jade | Level 19

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
Lapis Lazuli | Level 10
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
Rhodochrosite | Level 12

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

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

Mastering the WHERE Clause in PROC SQL

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.

Discussion stats
  • 8 replies
  • 916 views
  • 5 likes
  • 5 in conversation