topic Re: Can someone help me with the below array, which I am using the transpose 2 columns at the same t in SAS Programming

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

sasheadache — Thu, 30 Nov 2023 12:00:14 GMT

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;

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

Rick_SAS — Thu, 30 Nov 2023 11:52:36 GMT

> 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;

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

sasheadache — Thu, 30 Nov 2023 11:57:04 GMT

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

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

Rick_SAS — Thu, 30 Nov 2023 13:28:27 GMT

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;

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

sasheadache — Thu, 30 Nov 2023 13:35:57 GMT

Amazing thank you!!

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

Patrick — Thu, 30 Nov 2023 13:48:57 GMT

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;

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

Tom — Thu, 30 Nov 2023 18:30:10 GMT

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];
....