Can someone help me with the below array, which I am using the transpose 2 columns at the same time

[sasheadache]

I am using code similar to the below - this works, however it creates all values from Nmbr_ first and then does the same for Bal_. I want the columns to be created in the order of Nmbr_1/Bal_1/Nmbr_2/Bal_2 etc.

Note: The number of columns needs to be dynamic - so whilst I have put [5] in the sample code this number can change therefore I cannot use the retain function. (In my code I pass this from a macro token.)

Thanks in advance!

  data want;

    array Nmbr_[5] $14.; 
    array Bal_[5] ;

    do _n_ = 1 by 1 until(last.var2); 
      set input; 
      by var1 var2; 
      Nmbr_[_n_] = Nmbr; 
      Bal_[_n_] = Bal; 
    end;

    drop Nmbr Bal;

  run;

[Rick_SAS]

> I want the columns to be created in the order of Nmbr_1/Bal_1/Nmbr_2/Bal_2 etc.

Try adding this statement as the first line in the DATA step:

retain Nmbr_1 Bal_1 Nmbr_2 Bal_2 Nmbr_3 Bal_3 Nmbr_4 Bal_4 Nmbr_5 Bal_5;

[sasheadache]

Sorry should have specified this...... it needs to be dynamic because the number of columns could vary (its not always 5)

[Rick_SAS · Accepted solution]

OK, then build the RETAIN statement with the variable names and then put it into a macro variable:

%let numVars = 5;
data _null_;
length s $1000;
s = "retain ";
do i = 1 to &numVars;
   t = catt("Nmbr_", i, " Bal_", i);
   s = catx(' ', s, t);
end;
call symput("RetainStmt", s);
run;

%put &=RetainStmt;

data want;
    &RetainStmt;
    array Nmbr_[&numVars] $14.; 
    array Bal_[&numVars];
    /* ETC */
run;

[sasheadache]

Amazing thank you!!

[Patrick]

Alternatively use the retain statement directly with the array name. This will then retain all the array elements.

data want;
  array Nmbr_[&numVars] $14.; 
  array Bal_[&numVars];
  retain Nmbr_ Bal_;
  /* ETC */
run;

[Tom]

Do you know the number in advance?

%let n=5;

Or do you need to calculate it?

proc sql noprint;
select max(count) into :n trimmed 
from (select var1,var2,count(*) as count from have group by var1,var2)
;
quit;

Once you have the count you can generate the variable definitions.

You could use macro code, but you would need to use a macro definition to allow the use of the %DO loop.

length 
%do i=1 %to &n ;
  Nmbr_&i $14 Bal_&i 8 
%end;
;
  array Nmbr_[5] ;
  array Bal_[5] ;

Or you could generate the LENGTH statement with a data step.

filename code temp;
data _null_;
  file code column=cc;
  put 'length ' @;
  do i=1 to &n;
    put 'Nmbr_' i '$14 Bal_' i '8 ' @;
    if cc>65 then put / '  ' @ ;
  end;
  put ';' ;
run;
data want;
%include code / source2;
  array Nmbr_[&n];
  array Bal_[&n];
....