Assign values in macro variable to initial values of an array

[ukfirebrand]

Hi folks,

My first post and hopefully not a stupid question, even though I suspect the answer to be a resounding "can't be done!"

I have recently condensed several projects worth of work into a single EG project and the output from them uses some common values to generate views, tables and exports. For example, I used the following code to generate several tables with speciifc names where a variable is equal to the specific product.

data _null_;

array run_tables {n} $nn ("initval1","initval2",...,"initvaln");

array run_products {n} $nn ("prod1","prod2",...,"prodn");

do i = 1 to dim(run_tables);

rep_table = dequote(run_tables(i));

rep_product = dequote(run_products(i));

call execute ('%DS_OpenMonth (Table = '||rep_table||', Product = '||rep_product||', RepStrt = '||&HisStartdate.||', RepEnd = '||&HisEnddate.||')');

end;

run;

As the initial values of run_tables and run_products, amongst others, are always the same, but may shrink or grow, I'd like to be able to define the initial values via a macro variable at start up, e.g...

%let macro_tables = 'initval1','initval2',...'initvaln';

...and then use the macro variable elsewhere e.g.

data _null_;

array run_tables {n} $nn ("&macro_tables");...

I've tried various methods, including trying to resolve the macro variable.

Am I on a hiding to nothing or is there a way to do this I haven't discovered after numerous searches?

Many thanks in advance,

Mike...

[Astounding · Accepted solution]

There may be more to it than this, but a few items stand out concerning your ARRAY statement:

array run_tables {n} $nn ("&macro_tables");

The references to n and nn must be replaced with actual numbers (or references to macro variables that resolve to numbers, or else use * to indicate the number of elements in the array).  And the double quotes need to be removed.  Your macro variable &macro_tables contains all the quotes needed to form a valid ARRAY statement.

[ukfirebrand]

Hi Astounding,

Thanks for the prompt reply.

The references to n and nn are just generic replacements and not pertinent to the question.

However, I just tried using the following and although I'm pretty sure it didn't work before, it has now so.... massive thanks...

array run_tables {19} $100 (&macro_tables.);

Do you think it made a difference that I also changed the macro variable from %let macro_tables = "initval1","... to %let macro_tables = 'initval1','...?

Thanks again...

[Astounding]

Single vs. double quotes shouldn't make a difference in that context.  However, single quotes prevent resolution of macro variables so these would be very different:

%let m = '&name1', '&name2';

%let m = "&name1", "&name2";

[ukfirebrand]

By the way, some general feedback on your response.

I've now gone through all my code, replacing, amongst others, similar entries [to the below] and it has simplified the code immensely.

Great community here, Thanks again.!!

data _null_;

array run_tables {&macro_outvars.} $100 (&macro_tables.);

array run_products {&macro_outvars.} $100 (&macro_products.);

do i = 1 to dim(run_tables);

[Tom]

Here are some other ways to simplify more.

Why are the values in macro variables instead of a dataset?  It would be much easier if you could just pass in the list of tables in a SAS dataset.  Then you don't need to worry about macro variables at all.

data _null_;
  set tablelist;
  call execute( .... );
run;

Have you considered using a DO loop instead of an array?  You can use a DO statement to iterate over a list of values. Might not help much in your case since you seem to have pairs of values.

length run_table $32 ;
do run_table='table1','table2',.... ;
...
end;

 When using CALL EXECUTE() to generate a series of macro call it is best to wrap the macro name in %NRSTR() function. This will allow SAS to push the macro call onto the execution stack instead of running the macro when CALL EXECUTE runs and pushing the generated code onto the stack.  This prevents the code from running out of order and causing a lot of trouble.

call execute(cats('%nrstr(%DS_OpenMonth)'
,'(Table=',rep_table
,',Product=',rep_product
,',RepStrt=',"&HisStartdate"
,',RepEnd=',"&HisEnddate"
,')'
));

[ukfirebrand]

Hi Tom,

Some valid comments there but perhaps my use case helps define the solution more precisely.

Basically, I have around 30 macros all doing similar cuts against the same segments across various datasets, with the common segment being macro_products,

Each of those macros creates individual tables and requires outputting to a common data, relative to the product segement.

So 19 segments have 19 * x views and those views need outputting to 19 datafiles for use locally.

In order to simplify my code, I wanted to prescribe those segements and affiliated outcomes (tables names, out files, excel sheet names, etc) up front, so i could aviod replicating lists later in the code base. In my mind, this eased the administration burden by bringing every segment and associated output type into an upfront routine - my macro variables.

Needless to say, with the advice Astounding gave, I've been able to replace explicit array lists throughout my code, which iterated through the product segments using the upfront macro variables.

So, for example, the following [original] code was simplified and any, affiliated macro variable lists are updated in the same code location.

OLD::

data _null_;

array run_tables {21} $100 ("table1","table2",...,"tablen");

array run_products {21} $100 ("product1","product2",...,"productn");

do i = 1 to dim(run_tables);

rep_table = dequote(run_tables(i));

rep_product = dequote(run_products(i));

/* Current Period */

call execute ('%metrics_sum (TblNm = msum, Pos = '||rep_table||', Prod = '||rep_product||')');

end;

run;

NEW:

data _null_;

array run_tables {&macro_outvars.} $100 (&macro_tables.);

array run_products {&macro_outvars.} $100 (&macro_products.);

do i = 1 to dim(run_tables);

rep_table = dequote(run_tables(i));

rep_product = dequote(run_products(i));

/* Current Period */

call execute ('%metrics_sum (TblNm = msum, Pos = '||rep_table||', Prod = '||rep_product||')');

end;

run;

Everywhere I reference the list for run_tables / run_producst / etc, is now maintained in a single location in my code base.

[Tom]

So it looks like one of macros that you want to generate multiple calls for is defined like this:

%macro metrics_sum 
(TblNm = 
,Pos = 
,Prod = 
); 
...
%mend metrics_sum ;

So do the other macros also use the same POS and PROD macro parameters?  If so then I would store the metadata in a SAS dataset using those names as the variable names.  So the inital code would look something like this:

data run_list ;
  length pos $32 prod $50 ;
  input pos prod ;
cards;
table1 product1
table2 product2 
....
;

If you want to change the list of tables and products then just update the data lines. Note: You cannot do this inside of a macro since you cannot use CARDS (a.k.a. DATALINES) inside a macro.

Then when I wanted to generate calls to %METRICS_SUM() for each table*product combination I could use a simple data step to write the macro calls to a temporary file and then include the generated code.

filename code temp;
data _null_;
  set run_list;
  file code;
  put '%metrics_sum(TblNm=msum,' pos= ',' prod= ');' ;
run;
%include code / source2;