Looping a Parameter in a Program in EG

[paulrockliffe]

I have a program that was written for me that I want to modify to encompass a bit more flexibility.  The program takes a SAS Table and splits it into subsets based on the unique values in a selected column.  I use it to create Regional subsets of national data as an example.

The program has a parameter that defines the column to base the subsets on.  It also has a pair of parameters that sets the table that is being subset and a string that feeds into the resulting output data tables.

I want to loop the program so that it can create subsets based on more than one column.  And also loop it so that I can feed in several table/string pairs.

The program itself is quite complicated and I can easily copy-paste duplicate it to create the outputs I need, so I don't want to modify the code as it's simply not worth it for the extra simplicity and flexibility that looping it will give me.

Essentially I want the following:

Loop for each Input/Output Parameter pair = Table1,String1 Table 2,String2.

(Loop for each Column parameter = Region, Office

(Program))

So the solution is independent of the Program, other than passing values for the parameters into the Program.

I've done a load of Googling and not really found a solution that works, so figured it is time to ask the experts!

Thanks

[Kurt_Bremser]

So you'd want a macro the header of which would look like this:

%macro(dataset=,column=,string=);

?

[paulrockliffe]

Yes, possibly.  Ideally the values for the Macro variables would be defined within the code block rather than being pulled from a table.

My problem si I don't know how to wrap a Macro around the code block I think.

[Kurt_Bremser · Accepted solution]

It should not be that hard to do it, as you already have code that works for a single instance (as this is the most important prerequsite for macro development).

• identify the parts in your code that you would have to change manually if you run it for another instance
• replace these parts with macro variables, set the macro variables with %let before the code and test
• if the test works, replace the %let statements with the macro definition, and use the macro variable names as macro parameters; don't forget the %mend at the end
• Now you can use the macro either "manually", by writing the macro call for each instance yourself, or you can use call execute() to call the macro repeatedly from a data step (where you feed the parameters from datalines or an existing dataset.

Very simple example:

proc print data=sashelp.class;
run;

Step one: replace variable parts with macro vars:

%let dataset=sashelp.class;

proc print dataset=&dataset;
run;

Step two: wrap in macro:

%macro mymac(dataset);

proc print dataset=&dataset;
run;

%mend;

Call manually:

%mymac(sashelp.class)

Call from data step:

data _null_;
input dataset $32.;
call execute('%nrstr(%mymac('!!strip(dataset)!!'))');
datalines;
sashelp.class
sashelp.cars
;

The single quotes and the %nrstr() are necessary to avoid premature execution of macro triggers during the data step.

[paulrockliffe]

Perfect, a bit of messing around working out what was what and I have that working across multiple columns!

I then need to expand it to do the same thing for a range of tables.  I have two questions for that:

1.  Do I need to combine the entire code block (The bit that calls the Macro and the Macro) I have now as a second Macro?  Then call that in the same way?

2.  How do I modify the call block of code you've shown me so the datalines bit is feeding in linked pairs of values to two parameters?

Thanks again!

[Kurt_Bremser]

The datalines are a kind of "inline text file", so you can use the input statement like you use it when reading an external file. The default delimiter is a blank.

data _null_;
input value1 $ value2 $;
call execute('%nrstr(%mymac('!!strip(value1)!!','!!strip(value2)!!'))');
datalines;
XXX YYY
ZZZ AAA
;

[paulrockliffe]

Thanks again, that's pretty simple.  

Can you confirm that I combine my Macro Call and Macro as a new Macro and then call that to make this work?  

I've tried to do it that way and am hitting an issue, but can't see anything obviously wrong.  The effect is that the code I used to call the first Macro is no longer formatting the datalines with yellow highlighting, so it looks like I'm doing something wrong!

[Kurt_Bremser]

datalines and macro do not go together. You cannot use datalines in a macro, and you cannot use macro triggers in datalines.

[paulrockliffe]

OK, that explains why that doesn't work then!

What's the correct way to get this to work?  I'm not sure if I can modify the data line that's calling the Macro as I think that will result in every combination of my three parameters rather than every combination of the original parameter and the parameter pair. 

I'm going to mess around with that to see if there's a way to do it, but if there's a more appropriate approach it might save me some time.

Thanks again.

[Kurt_Bremser]

You need to supply a quick example of your macro structure (from outermost to innermost), and how you intend to pass parameters from the "open code" (outside the outermost macro) down to where the real work is done. Then it should be possible to devise a method for that.

[paulrockliffe]

Thanks, 

I've included the program block below:

data _null_;
input params $32.;
call execute('%nrstr(%ColumnSplit('!!strip(params)!!'))');
datalines;
Region
Sector
;

%MACRO ColumnSplit (params);


/* Sets Parameters - These are:
								The table to be split
								The location for the output tables to be created
								The parameter to split the table by
								The columns to be dropped from the output table
								A project identifier to add to the start of the resulting table names*/

%let indata=SASUSER.SOURCE;
%let outlib=SASUSER;
%let param=&params;
%let dropcolumns = Region Sector CCM Customer Office;
%let tablename=&prefix;

/* Creates a Lookup Table containing all unique values in the selected column */

proc sort
  data=&indata (keep=&param)
  out=lookup
  nodupkey
;
by &param;
run;

/* Modifies the contents of the lookup table so that the values are valid SAS names.  This is a necessary step as otherwise the output tables 
are built per word within the lookup table, which creates duplicates*/

data lookup;
set lookup;
vname = substr(translate(translate(translate(trim(&param),'','&'),'_',' '),'_','__'),1,32);
run;


/* Creates the output tables */

data _null_;

call execute ('data ');
do until (eof1);
  set lookup end=eof1;
  call execute("&outlib..&tablename._" !! trim(vname) !! ' ');
end;

call execute ("; set &indata.;");
do until(eof2);
  set lookup end=eof2;
  call execute("if &param = '" !! trim(&param) !! "' then output &outlib..&tablename._" !! trim(vname) !! ';');
end;

call execute('drop &dropcolumns; run;');
stop;

run;

%MEND ColumnSplit;

[paulrockliffe]

So I've started using this over a set of 6 tables.  Rather than working out how to loop them I figured I would setup 6 Program nodes manually and do it that way, it's pretty simple.  I've modified "indata" and "tablename" in each program to pickup a different table and output to a new set of tables.

Anyway, I've hit another issue.  

When I first run the code a as above it fails, but works on a second run.  If I create a second copy then it's first run pulls the parameters from the last run of the code.  The behaviour is consistent with the code creating the parameters after running the code creating the outputs, but I can't see how it's doing that.

Within the Macro the first thing that happens is that Indata and tablename are created, so I'm stumped!

Has anyone got any ideas what's causing it and how to fix it?

Thanks!

[Kurt_Bremser]

Always make sure to keep an eye on the scope of macro variables; declare variables used exclusively inside a macro as local (with %local), and declare variables that you need to "spill over" as global (%global). Try to avoid "invisible" side-effects by generous use of macro parameters (instead of just assuming that a global variable you use in the macro already exists)

Don't do

%macro test(param1);
.......
%if &param2 = .....
%mend;

but do

%macro test(param1,param2);
.......
%if &param2 = .....
%mend;

that makes param2 local to the macro, and forces you to be "honest" with your macro call (revealing everything that goes into the macro in the call itself).

Use diagnostic tools (options mprint mlogic symbolgen) to reveal the progress of your macro(s), and use statements like

%put _all_;

to show the current state of your macro symbol table.

[Kurt_Bremser]

I see this possible problem point:

call execute('drop &dropcolumns; run;');

Either you have to use %nrstr() to prevent premature resolution of the macro trigger, or resolve it right here, by using double quotes:

call execute("drop &dropcolumns; run;");

[paulrockliffe]

Thanks, that would make sense as I had this working and then added that bit to deal with the need to filter some tables that don't contain the columns I'm filtering by, I joined those columns back on then needed to drop them again.

I tried the double quote, that didn't work and I'm trying to get %nrstr() to work too, but struggling a bit.....