Retain array values with initital missing values

[Jeroen_ING]

1) create table with one number

I used this code:

proc sql;
create table max_table as select
max(numeric_column) as max_column
from anydataset;
quit;

in this created table max_table  I have 1 column (max_column) with a certain number (likely between 1-20, it may vary each time run the code)

2) create macro variable with this value

data _NULL_;
set max_table ;
call symput('max_arr', max_column);
run;

3) set array with this value and I want to retain the values. This is my current version that works.

data test2;
set test;

array date_s{&max_arr};
retain date_s1-date_s16;

/*other code that does calculations for the array*/

run;

note that I put the 1-16 manually, because at the moment the array has 16 values (the max from the inital table was 16)

4) what I want is to make this dynamic, like the following (this does not work)

data test2;
set test;

array date_s{&max_arr};
retain date_s1-date_s&max_arr;

/*other code that does calculations for the array*/

run;

I hope my explanation of the problem is clear.

How can I make this dynamic?

[FreelanceReinh · Accepted solution]

Hello @Jeroen_ING and welcome to the SAS Support Communities!

Unlike CALL SYMPUT, the newer CALL SYMPUTX routine removes leading blanks when a numeric value is assigned to a macro variable. Only these leading blanks must have been the reason why your RETAIN statement failed. So, you could just add the "X" after "SYMPUT".

But your code can be simplified in several ways:

1. Steps 1 and 2 can be replaced by a single PROC SQL step using an INTO clause:

   proc sql noprint;
   select max(numeric_column) into :max_arr trimmed
   from anydataset;
   quit;

2. The RETAIN statement accepts array names, so you don't need a variable list:

   retain date_s;

3. Alternatively, you can specify one or more initial values in the ARRAY statement. This implies a RETAIN for all array elements. That is, you can omit the RETAIN statement if you specify something like

   array date_s{&max_arr} (&max_arr*.);

[ballardw]

Step 4 may be extremely easy to accomplish if you have no other variables that start with the name of your array.

SAS provides a number of ways to create lists of variable. One of them is the : list which is the start of a variable followed by the : . That means "for this operation use all variables whose names start with the base".  And example with a data set you should have available.

data junk;
   set sashelp.class;
   array sss(10);
   retain sss: ;
   if _n_ le 10 then s[_n_] = weight;
run;

If you look at the output you can see for each of the first 10 records the weight is copied into one value then retained through the following iterations of the data step.

It is up to you to ensure that your array name does not start with the same characters or the other variables would be on the retain list and may result in unexpected output.

You may want to consider replacing your first two steps with :

proc sql noprint;
 select max(age) into : max_arr
  from sashelp.class;
quit;

The into : places the values of the select into a macro variable. Noprint means nothing gets printed to the results. I specifically said values because you can place multiple values into a single macro variable such as in this example:

proc sql noprint;
 select distinct age into : agelist separated by ' '
  from sashelp.class;
quit;

%put Age list is:&agelist;

[Jeroen_ING]

thanks, completely overlooked the fact that the space was causing the error.

I changed my code based on your improvements, thank you very much!!