SAS: lookup data inside a function/subroutine / return an array

Question

Suppose I like to do something like the following (with exemplary variable names for better readability):

1. take a parameter InParameter and match it to the variable MyVar1 in a dataset MyData

2. return all values for the variable MyVar2 for the filtered observations

3. from a subroutine/function

4. that i can use inside proc sql/datastep

This is what I got so far (clearly not working):

proc fcmp outlib=work.funcs.MyFunction;
    function MyFunction(InParameter $);
        array MyArray ... ; /* Here: Create an array with something like SELECT MyVar2 FROM MyData WHERE MyVar1 = Inparameter  */
        return(MyArray{});
    endsub;
    ;
quit;

options cmplib=work.funcs;

data MyOutput;
    set Somedata;
    if MyVar2 in MyFunction("H20") then output;
run;

In short:

• can data in datasets be accessed from inside a function/subroutine?
• can a function/subroutine return an array?

Thanks for your help!

Answer 1

We created a utility macro called %ds2list() that will perform your desired process. It doesn't use an array statement but it achieves the same result.

The macro simply returns values from a dataset in a list format. Here's an example of calling it:

%put %ds2list(iDs=sashelp.class, iField=name, iQuote=1);

This would return:

'Alfred','Alice','Barbara','Carol','Henry','James','Jane','Janet','Jeffrey','John','Joyce','Judy','Louise','Mary','Philip','Robert','Ronald','Thomas','William'

The default behavior for %ds2list() is to comma separate the returned values but it is very flexible. You can change the delimiter to a value of your choice (or no delimiter), you can turn the quotes on or off, or change them from single to double quotes, and you can provide any dataset options you would normally use on a set statement such as a where=() statement.

Additionally because the macro is pure macro code you can use this literally anywhere in SAS. In any proc/data/macro you like. We use it extensively for calls to ODBC passthrough when we have a large list of IDs we want to be returned.

Here's an example of how you could use it. First create a table that will contain values to compare against the list values:

data keep;
  input name $;
  datalines;
Alfred
Carol
Janet
run;

Iterate over the values we want to check against the list:

data want;
  set keep;
  if name in (%ds2list(iDs=sashelp.class, iField=name, iQuote=1, iDsOptions=where=(sex='F'))) then do;
    output;
  end;
run;

Returns:

Obs    name
===    =====
 1     Carol
 2     Janet

You can see Alfred was excluded from the result because he was filtered out by the where=() clause.

Here is the macro, I suggest putting it in your macro autocall library:

/***************************************************************************
**  PROGRAM: MACRO.DS2LIST.SAS
**
**  UTILITY PROGRAM THAT DETECTS RETURNS A LIST OF FIELD VALUES FROM A 
**  DATASET IN DELIMITED FORMAT.
**
**  PARAMETERS:
**  iDs       : THE LIBNAME.DATASET NAME THAT YOU WANT TO CHECK.
**  iField    : THE FIELD THAT CONTAINS THE VALUES YOU WANT RETURNED IN A 
**              DELIMITED FORMAT.
**  iDelimiter: DEFAULT IS A COMMA. THE DELIMITER TO USE FOR THE RETURNED LIST.
**  iDsOptions: ANY STANDARD DATASET OPTIONS THAT YOU WOULD LIKE TO APPLY SUCH 
**              AS A WHERE STATEMENT.
**  iQuote    : (0=NO,1=YES). DEFAULT=0/NO. DETERMINES WHETHER THE RETURNED 
**              LIST IS QUOTED OR NOT.
**  iQuoteChar: (SINGLE,DOUBLE) DEFAULT=SINGLE. SPECIFIES WHETHER SINGLE
**              OR DOUBLE QUOTES ARE USED WHEN QUOTING THE RETURNED LIST
**
*****************************************************************************/

%macro ds2list(iDs=, iField=, iDsOptions=, iDelimiter=%str(,), iQuote=0, iQuoteChar=single);
  %local dsid pos rc result cnt quotechar value;

  %let result=;
  %let cnt=0;

  %if &iQuote %then %do;
    %if "%upcase(&iQuoteChar)" eq "DOUBLE" %then %do;
      %let quotechar = %nrstr(%");
    %end;
    %else %if "%upcase(&iQuoteChar)" eq "SINGLE" %then %do;
      %let quotechar = %nrstr(%');
    %end;
    %else %do;
      %let quotechar = %nrstr(%");
      %put WARNING: MACRO.DS2LIST.SAS: PARAMETER IQUOTECHAR INCORRECT. DEFAULTED TO DOUBLE;
    %end;
  %end;
  %else %do;
    %let quotechar = ;
  %end;

  /*
  ** ENSURE ALL THE REQUIRED PARAMETERS WERE PASSED IN.
  */
  %if "&iDs" ne "" and "&iField" ne "" %then %do;

    %let dsid=%sysfunc(open(&iDs(&iDsOptions),i));
    %if &dsid %then %do;

      %let pos=%sysfunc(varnum(&dsid,&iField));
      %if &pos %then %do;

        %let rc=%sysfunc(fetch(&dsid));
        %do %while (&rc eq 0);

          %if "%sysfunc(vartype(&dsid,&pos))" = "C" %then %do;
            %let value = %qsysfunc(getvarc(&dsid,&pos));
            %if "%trim(&value)" ne "" %then %do;
              %let value = %qtrim(&value);
            %end;
          %end;
          %else %do;
            %let value = %sysfunc(getvarn(&dsid,&pos));
          %end;

          /* WHITESPACE/CARRIAGE RETURNS REMOVED IN THE BELOW LINE */
          /* TO ENSURE NO WHITESPACE IS RETURNED IN THE OUTPUT.    */
          %if &cnt ne 0 %then %do;%unquote(&iDelimiter)%end;%unquote(&quotechar&value&quotechar.)

          %let cnt = %eval(&cnt + 1);
          %let rc  = %sysfunc(fetch(&dsid));
        %end;

        %if &rc ne -1 %then %do;
          %put WARNING: MACRO.DS2LIST.SAS: %sysfunc(sysmsg());
        %end;

      %end;
      %else %do;
        %put ERROR: MACRO.DS2LIST.SAS: FIELD &iField NOT FOUND IN DATASET %upcase(&iDs).;
      %end;
    %end;
    %else %do;
      %put ERROR: MACRO.DS2LIST.SAS: DATASET %upcase(&iDs) COULD NOT BE OPENED.;
    %end;

    %let rc=%sysfunc(close(&dsid));

  %end;
  %else %do;
    %put ERROR: MACRO.DS2LIST.SAS: YOU MUST SPECIFY BOTH THE IDS AND IFIELD PARAMETERS TO CALL THIS MACRO.;
  %end;

%mend;