Finding a value in all datasets in all variables - revisited

[tomrvincent]

Here's my take on it.  Finds all tables with at least one char field with the selected value.

%macro findvalue;

	%let value=vincent;

	proc sql noprint;

		SELECT distinct cats(libname,'.',memname),   'work.' || memname
		into :libmemname1-,:worklibmemname1-
		FROM dictionary.columns t1
		WHERE type = 'char'
			AND libname='ELTSSDE';

	%let memobs = &sqlobs;

	%do j=1 %to &memobs;

		SELECT name
			into :name1-
			FROM dictionary.columns t1
			WHERE type = 'char' 
				and cats(libname,'.',memname)="&&libmemname&j"
				AND libname='ELTSSDE';

		%let colobs = &sqlobs;

		%if &colobs=0 %then %return;

		create table &&worklibmemname&j as
			select * from &&libmemname&j

/*	%put &&worklibmemname&j &&libmemname&j;*/

		%do i=1 %to &colobs;

			%if &i=1 %then %let whereor = where ; %else %let whereor = or;

			&whereor upcase(&&name&i ) = upcase("&value")

/*			%put &whereor &&name&i;*/

		%end;

			;

		%if &sqlobs=0 %then drop table &&worklibmemname&j;
			;

	%end;

%mend findvalue;

%findvalue;

[ballardw]

Restrict the variable selection to only those with a length as long or greater as the value you are searching for might help increase efficiency a bit.

WHERE type = 'char'

   and length ge length("&value")

The actual value would be much better off as a Parameter to the macro.

%macro findvalue (value);

sort of confused about a specific libname in the WHERE clause though.

If a character variable is found in the WORk library then there isn't a need that I can see for   'work.' || memname as the cats(libname,'.',memname) should create "work.memname".

[tomrvincent]

@ballardw 

I like the length addition.  Thanks for that.

Yes, adding parameters to the macro was my next step...didn't need them for first version.

The libname is needed because work is the destination library, not the source. 

CATS would work as well but adds nothing.

[ChrisNZ]

Also you seem to use %return wrongly, which will affect the results by ending the macro prematurely.

This version would work as intended, be faster and be clearer imho:

%macro findvalue(lib=, value=, icase=1);

  %local memnb memno colnb colno;

  proc sql noprint;

    select distinct MEMNAME
    into :memname1-
    from DICTIONARY.COLUMNS 
    where LIBNAME eq "&lib" 
      and TYPE    eq 'char' 
      and LENGTH  ge length("&value");

    %let memnb = &sqlobs.;

    %do memno=1 %to &memnb.;

      select NAME into :varname1-
        from DICTIONARY.COLUMNS 
        where LIBNAME eq "&lib" 
          and MEMNAME eq "&&memname&memno"
          and TYPE    eq 'char' 
          and LENGTH  ge length("&value");

      %let colnb = &sqlobs.;

      %if &colnb. %then %do;
        create table WORK.ZZ&&memname&memno. as
          select * from &lib..&&memname&memno.
          %do colno=1 %to &colnb.;
            %if &colno=1 %then where ; %else or;
            %if &icase. %then 
              upcase(&&varname&colno. ) = upcase("&value");
            %else
              &&varname&colno. = "&value";
          %end;
          ;

        %if &sqlobs.=0 %then 
          drop table WORK.ZZ&&memname&memno.; ;

      %end;

    %end;

  quit;

%mend findvalue;

%findvalue(lib=SASHELP, value=Alice);

This version is not satisfactory for me as it creates global macro variables.

I would remedy this point as well before making the macro available.

[ChrisNZ]

Improved version:

- No macro language

- Much faster as the dictionary is only queried once

- Much more resistant to weird values being searched

- Option for case-sensitive/insensitive comparison 

- More flexible as different comparison operators can be used (EQ or =:  or ? for example)

[Edited to add char/num search option]

%macro FindAValue(lib=, type=C, value=, icase=1, comparison=EQ);

  %macro _; %mend _;

  proc sql ;
    create table __FINDAVALUE__ as
    select MEMNAME, NAME
    from DICTIONARY.COLUMNS 
    where LIBNAME eq "&lib " 
      and TYPE    eq %if &type.=C %then 'char'
      and LENGTH  ge %length(%superq(value))
      ; %else 'num' ;
    order by MEMNAME;
    %if ^&sqlobs. %then %do;
      drop table __FINDAVALUE__; quit;
      %return;
    %end;
  quit;

  data _null_;  
    set __FINDAVALUE__ ;
    by MEMNAME;
    if first.MEMNAME then call execute('data ZZ'||MEMNAME||"; set &lib.."||MEMNAME||'; where ');
    else call execute(' or ');
    %if &type.=C & &icase. %then 
      call execute('upcase('||NAME||") &comparison "||upcase(quote(trim(symget('value')))) );
    %else %if &type.=C %then
      call execute(           NAME||"  &comparison "||       quote(trim(symget('value')))   );
    %else 
      call execute(           NAME||"  &comparison "||             trim(symget('value'))    );
    ;
    if last.MEMNAME then call execute(';data _null_;' 
     ||'if NOBS=0 then call execute("proc delete data=ZZ'||MEMNAME||';run;");'
     ||'if 0 then set ZZ'||MEMNAME||' nobs=NOBS; stop; run;');
   run;

   proc delete data=__FINDAVALUE__; run;

%mend FindAValue;
                                                                  
%FindAValue(lib=SASHELP, value=Alice, icase=1, comparison=?);

%FindAValue(lib=SASHELP, value=11,  type=N, comparison=eq);

[ScottBass]

Well this looked kind of fun 🙂

Here's my take on it.  Perhaps someone can suggest performance improvements but it has acceptable performance on SASHELP.  %code1 performs better but can miss hits if you're searching for multiple strings.

I'll leave it to you to finish "macro-izing" it, such as wrapping it in a top level macro, case-insensitive search, etc.

Go here to download the macros:  https://github.com/scottbass/SAS/tree/master/Macro

* list of datasets ;
proc sql noprint;
   select catx('.',libname,memname) 
   into   :datasets separated by ' '
   from   dictionary.tables
   where  libname="SASHELP" 
     and  memtype='DATA'
   ;
quit;

%put &datasets;

* macro to process the datasets ;
%macro code1;
data temp;
   length _dataset_ $32 _variable_ $32 _value_ $200 _dsname_ $41;
   set &word indsname=dsname;
   array vars{*} _character_;
   do i=1 to dim(vars);
      if vars{i} in (%seplist(&value,indlm=^,nest=q)) then do;
         _dataset_=dsname;
         _variable_=vname(vars{i});
         _value_=vars{i};
         output;
         stop;
      end;
   end;
   keep _dataset_ _variable_ _value_;
run;
proc append 
   base=results1 
   data=temp (rename=(_dataset_=dataset _variable_=variable _value_=value))
;
run;
%mend;

* Alternative, slower but more complete ;
%macro code2;
data temp;
   length _dataset_ $32 _variable_ $32 _value_ $200 _dsname_ $41;
   set &word indsname=dsname;
   array vars{*} _character_;
   do i=1 to dim(vars);
      if vars{i} in (%seplist(&value,indlm=^,nest=q)) then do;
         _dataset_=dsname;
         _variable_=vname(vars{i});
         _value_=vars{i};
         output;
         * stop;
      end;
   end;
   keep _dataset_ _variable_ _value_;
run;
proc sort noduprecs;
   by _all_;
run;
proc append 
   base=results2 
   data=temp (rename=(_dataset_=dataset _variable_=variable _value_=value))
;
run;
%mend;

%kill(data=results1 results2);

%let value=Ketchikan^New York^Alice^IBM;

* testing ;
/*%loop(sashelp.zipcode,mname=code1)*/

* the whole shebang ;
* note this misses some hits in zipcode because the stop statement stops on the first hit ;
%loop(&datasets,mname=code1)

* alternative approach ;
%loop(&datasets,mname=code2)