BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
sasheadache
Obsidian | Level 7

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Rick_SAS
SAS Super FREQ

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;

View solution in original post

6 REPLIES 6
Rick_SAS
SAS Super FREQ

> 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
Obsidian | Level 7

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

Rick_SAS
SAS Super FREQ

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;
Patrick
Opal | Level 21

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
Super User Tom
Super User

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

 

sas-innovate-wordmark-2025-midnight.png

Register Today!

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.


Register now!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 6 replies
  • 1464 views
  • 1 like
  • 4 in conversation