
/*** HELP START ***//*
 
## >>> `%findDSwithVarVal()` macro: <<< <a name="finddswithvarval-macro"></a> #######################  

The findDSwithVarVal() macro searches for all 
datasets (available for a given session) containing 
a variable of a given value.

The value search is case sensitive - but can be altered with `IC=` parameter.
The value search keeps leading blanks - but can be altered with `TB=` parameter.
The value search compares full value - but can be altered with `CTS=` parameter.

The default variable type is `char`, the `type=` parameter allows 
to change it (possible values are `char` and `num`), the parameter is case sensitive.

Only datasets are searched, views are not included.

During the process two temporary datasets named: 
`WORK._` (single underscore) and `WORK.__` (double underscore) 
are generated. The datasets are deleted at the end of the process.

By default search results are stored in the `WORK.RESULT` dataset.
Name of the dataset can be altered with `result=` parameter.
The dataset with result contains two variables:
`datasetName` - names of datasets,
`firstObservation` - the firs occurrence of the value.

See examples below for the details.

The `%findDSwithVarVal()` macro does not execute as a pure macro code.

### SYNTAX: ###################################################################

The basic syntax is the following, the `<...>` means optional parameters:
~~~~~~~~~~~~~~~~~~~~~~~sas
%findDSwithVarVal(
    variable
   ,value
  <,type=>
  <,ic=>
  <,tb=>
  <,cts=>
  <,lib=>
  <,result=>
)
~~~~~~~~~~~~~~~~~~~~~~~

**Arguments description**:

1. `variable`     - *Required*, name of variable to be searched.

2. `value`        - *Required*, the value to be searched.

*. `type`         - *Optional*, default value is `char`.
                    Indicates which type is the searched value.
                    Possible values are `char` and `num`, 
                    the parameter is case sensitive. 

*. `ic`           - *Optional*, "Ignore Cases", default value is `0`.
                    Indicates should the search ignore cases of the text values.
                    Possible values are `0` and `1`.

*. `tb`           - *Optional*, "Trim Blanks", default value is `0`.
                    Indicates should the search trim leading and trailing
                    blanks of the text values.
                    Possible values are `0` and `1`.

*. `cts`          - *Optional*, "Compare To Shorter", default value is `0`.
                    IF set to `1` execute value comparison as `=:` for the text value.
                    Possible values are `0` and `1`.
                    See examples.

*. `lib`          - *Optional*, default value is missing.
                    If not empty narrows the search to a particular library.

*. `result`       - *Optional*, default value is `WORK.RESULT`.
                    Is the name of the dataset with results.

---

*//*** HELP END ***/

/* finddswithvarval.sas */

%macro findDSwithVarVal(
  variable
, value
, type=char
, ic=0
, tb=0
, cts=0
, lib=
, result=WORK.RESULT) 
/ 
secure 
minoperator
des='Macro %findDSwithVarVal() searches for datasets (available for a given session) containing a variable of a given value.'
;

  %local _ n len tmp_NS tmp_OBS tmp_SL obsInResult;
  %let obsInResult = .;
  %let tmp_NS = %sysfunc(getoption(NOTES)) %sysfunc(getoption(SOURCE));
  %let tmp_OBS = %sysfunc(getoption(OBS));
  /*%put _local_;*/

  %if NOT(%superq(type) IN (char num)) %then
    %do;
      %put WARNING:[&sysmacroname.] Illegal value of TYPE parameter, choose one from: char, num;
      %put WARNING-[&sysmacroname.] The value is: %superq(type);
      %put WARNING-[&sysmacroname.] and will be changed to default: char.;
      %let type = char;
    %end;

  %if NOT(%superq(ic) IN (0 1)) %then
    %do;
      %put WARNING:[&sysmacroname.] Illegal value of IC parameter, choose one from: 0, 1;
      %put WARNING-[&sysmacroname.] The value is: %superq(ic);
      %put WARNING-[&sysmacroname.] and will be changed to default: 0;
      %let ic = 0;
    %end;
  %if &IC.=1 %then
    %let IC=UPCASE;
  %else
    %let IC=;

  %if NOT(%superq(tb) IN (0 1)) %then
    %do;
      %put WARNING:[&sysmacroname.] Illegal value of TB parameter, choose one from: 0, 1;
      %put WARNING-[&sysmacroname.] The value is: %superq(ic);
      %put WARNING-[&sysmacroname.] and will be changed to default: 0;
      %let tb = 0;
    %end;
  %if &TB.=1 %then
    %let TB=STRIP;
  %else
    %let TB=;

  %if NOT(%superq(cts) IN (0 1)) %then
    %do;
      %put WARNING:[&sysmacroname.] Illegal value of CTS parameter, choose one from: 0, 1;
      %put WARNING-[&sysmacroname.] The value is: %superq(CTS);
      %put WARNING-[&sysmacroname.] and will be changed to default: 0;
      %let cts = 0;
    %end;
  %if &CTS.=1 %then
    %let CTS=:;
  %else
    %let CTS=;


  options NoNOTES NoSOURCE;

  data _null_;
    length variable $ 32 result $ 41 lib $ 8;

    if getoption('validvarname') NE "ANY" then
      do;
        variable = compress(symget("variable"),"_",'KAD');
        call symputX("variable", variable, "L" );
      end;

    result = compress(symget("result"),"_.",'KAD');
    if result=" " then result="WORK.RESULT";
    call symputX("result", result, "L");

    lib = compress(symget("lib"),"_",'KAD');
    call symputX("lib", lib, "L" );
  run;

  options &tmp_NS.;
  %put NOTE:[&sysmacroname.] Parameters are:;
  %put NOTE-[&sysmacroname.] Variable: &variable.;
  %put NOTE-[&sysmacroname.] Value: %superq(value);
  %put NOTE-[&sysmacroname.] Type: &type.;
  %if %superq(lib) NE %then
    %do;
      %put NOTE-[&sysmacroname.] Library: %superq(lib);
    %end;
  options NoNOTES NoSOURCE;

  %if %superq(variable)= %then
    %do;
      %put ERROR:[&sysmacroname.] Variable name can not be empty. Exiting!;
      %goTo ENDOFMACRO;
    %end;

  %let tmp_SL=&syslast.;
  %let syslast=&result.;
  %let result=&syslast.;
  %let syslast=&tmp_SL.;

  data WORK._;
    "%unquote(&variable.)"n = " ";
  run;

  data WORK.__;
    "%unquote(&variable.)"n = .;
  run;

  %let n=0;
  proc sql noprint;
  select 
    catx(".",libname,strip(quote(strip(memname),"'"))!!"n") /* to handle validmemname=EXTEND */
    ,max(length)
  into :dslist1-,
       :len
  from dictionary.columns
  where upcase(name) = upcase(symget("variable"))
    and type="&type." 
    and memtype='DATA'
  %if %superq(lib) NE %then
    %do;
      and libname = %upcase("&lib.")
    %end;
  ;
  %let n=&SQLobs;
  quit;

  /*%put !!! &n. !!! "&&dslist&n." !!!;*/

  %if &n. AND (&&dslist&n. ne ''n) %then
    %do;
      options obs=1;
      data &result.(where=(datasetName not in ("WORK._" "WORK.__")));
        length 
          datasetName $ 41 
          _ %if &type.=char %then $; &len.
        ;
        label
          datasetName = 
            "Datasets containing variable %upcase(&variable.) with value: '%superq(value)'"
          firstObservation = 
            "The first occurrence of the searched value" 
        ;

        set
            %do n=1 %to &n.;
              &&dslist&n.(keep="%unquote(&variable.)"n rename=("%unquote(&variable.)"n=_)) /* to handle validvarname=ANY */
            %end;
          indsname=indsname
          curobs=curobs
          end=end
        ;
        %if &type.=num %then
          %do;
            where _ = symgetn('value');
          %end;
        %if &type.=char %then
          %do;
            /* trim(upcase(_) =: */
            where &TB.(&IC.(_)) =&CTS. &TB.(&IC.(symget('value')));
          %end;

        datasetName = indsname;
        firstObservation = curobs;
        keep datasetName firstObservation;
      run;
      data _null_;
        call symputX('obsInResult', nobs, "L");
        stop;
        set &result. nobs=nobs;
      run;
      options obs=&tmp_OBS.;

      %put %str(     )[&sysmacroname.] Results provided in &result. dataset with &obsInResult. observation(s).;
    %end;
  %else
    %put %str(     )[&sysmacroname.] No results found.;

  proc delete data=_ __;
  run;
  options &tmp_NS.;
  run;

  %ENDOFMACRO:
  options &tmp_NS. obs=&tmp_OBS.;
  run;
%mend findDSwithVarVal;

/* test cases:
options NOTES SOURCE obs=MAX;
%let x=%str(;data _null_; run;);

%findDSwithVarVal(code, CM8, result=WORK.RESULT1)&x.
%findDSwithVarVal(name, John, ic=1, result=WORK.RESULT2)&x.
%findDSwithVarVal(age, 14, type=num, result=WORK.RESULT3)&x.

%findDSwithVarVal(age, ABC, type=num, result=WORK.RESULT3)&x.

%findDSwithVarVal(age, ., type=num, result=WORK.RESULT4)&x.
%findDSwithVarVal(name, , result=WORK.RESULT5)&x.

%findDSwithVarVal(, 14, type=num, result=WORK.RESULT3)&x.

%findDSwithVarVal(!!!, 14, type=num, result=WORK.RESULT3)&x.

%findDSwithVarVal(age, 14, type=num, result=)&x.

%findDSwithVarVal(name, Jo, ic=1, tb=1, cts=1, lib=NOEXIST)&x.

%findDSwithVarVal(name, Jo, ic=1, tb=1, cts=1, lib=!!!)&x.

%findDSwithVarVal(jkvadhsjkfgvhasdjkfghjkh, Jo, ic=1, tb=1, cts=1, lib=!!!)&x.


%put %sysfunc(getoption(validvarname)) %sysfunc(getoption(validmemname));
options validvarname=ANY validmemname=EXTEND;

data 'A B C D'n;
  "name ,is"n = "bart";
run;

data "ABC"n;
  "x"n=42;
  "x y"n=42;
  "name ,is"n='Bart';
run;

%findDSwithVarVal(%str(name ,is), BART, ic=1, tb=1, cts=1)&x.

options validvarname=V7 validmemname=COMPATIBLE;
*/

/*** HELP START ***//*
 
### EXAMPLES AND USECASES: ####################################################

**EXAMPLE 1.** Search variable `NAME` containing value `John`:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~sas
  %findDSwithVarVal(name, John)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


**EXAMPLE 2.** Search numeric variable `AGE` containing value `14`:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~sas
  %findDSwithVarVal(age, 14, type=num)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


**EXAMPLE 3.** Search numeric variable `SCORE` with missing value:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~sas
    data TEST;
      score=17; output;
      score=42; output;
      score=. ; output;
    run;
    
    %findDSwithVarVal(score, ., type=num, result=WORK.MissingScore)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


**EXAMPLE 4.** Search library `WORK` for variable `NAME` starting with value `Jo` 
               ignoring cases and trimming blanks from value:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~sas
  data A;
    name="Joanna";
  data B;
    name="john";
  data C;
    name="  Joseph";
  data D;
    name="  joe";
  run;

  %findDSwithVarVal(name, Jo, ic=1, tb=1, cts=1, lib=WORK)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

---

*//*** HELP END ***/

/**###################################################################**/
/*                                                                     */
/*  Copyright Bartosz Jablonski, since 2023.                           */
/*                                                                     */
/*  Code is under the MIT license. If you want - you can use it.       */
/*  But it comes with absolutely no warranty whatsoever.               */
/*  If you cause any damage or something - it will be your own fault.  */
/*  You've been warned! You are using it on your own risk.             */
/*  However, if you decide to use it don't forget to mention author.   */
/*  Bartosz Jablonski (yabwon@gmail.com)                               */
/*                                                                     */
/**###################################################################**/
