A macro function to produce a macro variable from a data variable

Question

data sample;
    input x $;
    datalines;
one
two
three
;

%macro variable_to_macvar(variable=, dataset=);
    proc sql noprint;
        select &variable into : outlist separated by ' ' 
        from &dataset;
    quit;
&outlist
%mend variable_to_macvar;

%put %variable_to_macvar(variable=x, dataset=sample);

Expected output: one two three. Instead I get an error. Why? Is this fixable?

I've successfully created other macros of a very similar form, where the function "returns" a value using the &macrovariable at the end of the macro without a semicolon. For example, here is a similar type of function that works:

%macro zcat(first=5, last=15, prefix=freq); 
    %let x=;   
    %do i = &first %to &last;
        %let x=&x &prefix.&i;
    %end;
    &x
%mend zcat;
%put %zcat();

Answer 1

We have a utility macro that is probably one of our most used pieces of code that does this for us. It is similar to the code that @user667489 provided but includes some nice features including error catching, allows both character and numeric vars, allows you to specify seperators, quotes, quote characters, filters to the dataset, etc....

We just put this macro in our autocall library so that it's avaialble to all of our programs. Some examples of running the macro:

---

Example 1 - Default behaviour:

%put %variable_to_macvar(var=x, ds=samplex);

Result 1:

one,two,three

Not quite the desired output as the default seperator is a comma, this is easily changed though...

---

Example 2 - Specify to use a space character as a delimiter:

%put %ds2list(iDs=samplex, iField=x, iDelimiter=%str( ));

Result 2:

one two three

---

Example 3 - Quoting & example usage

data names;
  input name $;
  datalines;
John
Jim
Frankie
;
run;

%put %ds2list(iDs=names, iField=name, iQuote=1);

proc sql noprint;
  create table xx as 
  select *
  from sashelp.class
  where name in (%ds2list(iDs=names, iField=name, iQuote=1))
  ;
quit;

Result 3:

The below is printed to the log:

'John','Jim','Frankie'

Notice how we don't need to even save the result to a macro variable to use it in the SQL statement! Swweeet! This works just as well for SQL passthrough queries, and any other data step or proc statement that you can throw it at. In the above example, a single row is returned as 'John' is the only match found...

---

Anyway, that's our solution here... been using this for >10 years and works well for me. Here is the macro:

/***************************************************************************
**  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
**
*****************************************************************************
** VERSION:
**  1.8 MODIFIED: 11-OCT-2010  BY: KN
**      ALLOW BLANK CHARACTER VALUES AND ALSO REMOVED TRAILING
**      ALLOW PARENTHESES IN CHARACTER VALUES
*****************************************************************************/

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

  %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 = %qsysfunc(cats(%nrstr(&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;

Answer 2

You cannot execute a macro that involves running a proc or a data step in the way that you're trying to do here. You would need to use something like %sysfunc(dosubl(proc sql...)) in order for that to work (assuming you have SAS 9.3+ - see Joe's answer above). Otherwise, you can't use proc sql within a function-style macro.

More details about dosubl: http://support.sas.com/documentation/cdl/en/lefunctionsref/67398/HTML/default/viewer.htm#p09dcftd1xxg1kn1brnjyc0q93yk.htm

It would be a bit fiddly, but if you really wanted to make this work as a function-style macro in earlier versions of SAS, you could construct it using the open, fetchobs and getvarc functions instead.

Update: Here's an example (using call set rather than getvarc, as this turned out to be simpler), in case anyone needs to do this in SAS 9.2 or earlier.

%macro variable_to_macvar(var,ds);
    %local rc dsid i;
    %let &var =;
    %global outlist;
    %let outlist=;
    %let dsid = %sysfunc(open(&ds,i));
    %syscall set(dsid);
    %let rc = 0;
    %let i = 0;
    %do %while(&rc = 0);
        %let i = %eval(&i + 1);
        %let rc = %sysfunc(fetchobs(&dsid,&i));
        %if &rc = 0 %then %let outlist = &outlist &&&var;
    %end;
    %let rc = %sysfunc(close(&dsid));
    &outlist
%mend;

%put %variable_to_macvar(var=x, ds=sample);

Now works for views as well as ordinary datasets.

Answer 3

Alter your code at the end to

%global outlist;
%variable_to_macvar(variable=x, dataset=sample);
%put &outlist;

The %put wants to resolve only a macro variable or a single value. It cannot call a procedure. So call your macro and then print the result.

Also, delete the &outlist from the macro definition. Sorry I missed that initially.

EDIT: Alternative.

Change your macro definition to

%macro variable_to_macvar(variable=, dataset=);
    proc sql noprint;
        select &variable into : outlist separated by ' ' 
        from &dataset;
    quit;
%put &outlist
%mend variable_to_macvar;

Just do the %put inside the macro.

%variable_to_macvar(variable=x, dataset=sample);

will print the string to the log.

Answer 4

DOSUBL is available (but experimental) in 9.3 (at least, 9.3TS1M2, which I have). This is how you'd do it.

data sample;
    input x $;
    datalines;
one
two
three
;

%macro variable_to_macvar(variable=, dataset=);
   %let rc=%sysfunc(dosubl(%str(
    proc sql noprint;
        select &variable into : outlist separated by ' ' 
        from &dataset;
    quit;
    )));
&outlist
%mend variable_to_macvar;

%put %variable_to_macvar(variable=x, dataset=sample);;

If you can't use DOSUBL, or want to avoid experimental things, you can do this with PROC FCMP rather than a macro. If you like to write functions, PROC FCMP is probably for you: actually being able to write functions, rather than having to deal with the annoyances of the macro language.