Text manipulation of macro list variables to stack datasets with automated names

Question

I have written a macro that accepts a list of variables, runs a proc mixed model using each variable as a predictor, and then exports the results to a dataset with the variable name appended to it. I am trying to figure out how to stack the results from all of the variables in a single data set.

Here is the macro:

%macro cogTraj(cog,varlist);
    %let j = 1;
    %let var = %scan(&varlist, %eval(&j));
    %let solution = sol;
    %let outsol = &solution.&var.;

        %do %while (&var ne );

                proc mixed data = datuse;
                model &cog = &var  &var*year  /solution cl;
                random int year/subject = id;
                ods output SolutionF = &outsol;
                run;

        %let j = %eval(&j + 1);
        %let var = %scan(&varlist, %eval(&j));
        %let outsol = &solution.&var.;
        %end;

%mend;


   /* Example */
   %cogTraj(mmmscore, varlist = bio1 bio2 bio3);

The result would be the creation of Solbio1, Solbio2, and Solbio3.

I have created a macro variable containing the "varlist" (Ideally, I'd like to input a macro variable list as the argument but I haven't figured out how to deal with the scoping):

%let biolist = bio1 bio2 bio3;

I want to stack Solbio1, Solbio2, and Solbio3 by using text manipulation to add "Sol" to the beginning of each variable. I tried the following, outside of any data step or macro:

 %let biolistsol = %add_string( &biolist, Sol, location = prefix);

without success.

Ultimately, I want to do something like this;

    data Solbio_stack;
    set %biolistsol;
    run;

with the result being a single dataset in which Solbio1, Solbio2, and Solbio3 are stacked, but I'm sure I don't have the right syntax.

Can anyone help me with the text string/dataset stacking issue? I would be extra happy if I could figure out how to change the macro to accept %biolist as the argument, rather than writing out the list variables as an argument for the macro.

Joe Joe · Accepted Answer · 2014-06-18T18:54:14

I would approach this differently. A good approach for the problem is to drive it with a dataset; that's what SAS is good at, really, and it's very easy.

First, construct a dataset that has a row for each variable you're running this on, and a variable name that contains the variable name (one per row). You might be able to construct this using PROC CONTENTS or sashelp.vtable or dictionary.tables, if you're using a set of variables from one particular dataset. It can also come from a spreadsheet you import, or a text file, or anything else really - or just written as datalines, as below.

So your example would have this dataset:

data vars_run;
input name $ cog $;
datalines;
bio1 mmmscore
bio2 mmmscore
bio3 mmmscore
;;;;
run;

If your 'cog' is fairly consistent you don't need to put it in the data, if it is something that might change you might also have a variable for it in the data. I do in the above example include it.

Then, you write the macro so it does one pass on the PROC MIXED - ie, the inner part of the %do loop.

%macro cogTraj(cog=,var=, sol=sol);
     proc mixed data = datuse;
      model &cog = &var  &var*year  /solution cl;
      random int year/subject = id;
      ods output SolutionF = &sol.&var.;
     run;
%mend cogTraj;

I put the default for &sol in there. Now, you generate one call to the macro from each row in your dataset. You also generate a list of the sol sets.

proc sql;
 select cats('%cogTraj(cog=',cog,',var=',name,',sol=sol)')
  into :callList
  sepearated by ' '
  from have;
 select cats('sol',name') into :solList separated by ' '
   from have;
quit;

Next, you run the macro:

&callList.

And then you can do this:

data sol_all;
 set &solList.;
run;

All done, and a lot less macro variable parsing which is messy and annoying.

Text manipulation of macro list variables to stack datasets with automated names

1 Answers