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;
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;
> 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;
Sorry should have specified this...... it needs to be dynamic because the number of columns could vary (its not always 5)
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;
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;
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];
....
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.