Use do loop index in new variable name

[MarkusB]

Hello,

I'm looking for a solution to create new variables using a do loop. The names of the new variables should include the index of the do loop. Here's my example code:

data have;
input M Q;
datalines;
1   10
2   30
;

data want;
set have;
	do i = 1 to 2;
		if M=i then QM_i=Q;
		else QM_i=0;
	end;
run;

Of course, the program does only generate one new variable QM_i (besides the index variable i) because it does not recognize the index i in the variable name:

	M	Q	i	QM_i
1	1	10	3	0
2	2	30	3	30

Rather, I would like to get:

	M	Q	i	QM_1	QM_2
1	1	10	3	10	0
2	2	30	3	0	30

Any help is greatly appreciated!

[novinosrin · Accepted solution]

Hi @MarkusB  I'm afraid you can't quite generate and execute at execution time within a datastep or a SQL. This is where you need the macro processor to get involved and generate the statements at compile time and execute later.

For example

data have;
input M Q;
datalines;
1   10
2   30
;
%macro t;
data want;
set have;
	%do i = 1 %to 2;
		if M=&i then QM_&i=Q;
		else QM_&i=0;
	%end;
run;
%mend t;

%t

[MarkusB]

This works - thank you all for your replies!

[andreas_lds]

You have to define and use an array:

data want;
	set have;
	
	array new_vars[2] qm_1 qm_2;
	
	do i = 1 to 2;
		if M=i then new_vars[i]=Q;
		else new_vars[i]=0;
	end;
run;

EDIT: Renamed variables qm1 => qm_1

[hashman]

@MarkusB: Try this:

data have ;                                                                                                                                                                                                                                                     
  input M Q ;                                                                                                                                                                                                                                                   
  cards ;                                                                                                                                                                                                                                                       
1   10                                                                                                                                                                                                                                                          
2   30                                                                                                                                                                                                                                                          
;                                                                                                                                                                                                                                                               
                                                                                                                                                                                                                                                                
data want ;                                                                                                                                                                                                                                                     
  set have ;                                                                                                                                                                                                                                                    
  array qm qm_1 - qm_2 ;                                                                                                                                                                                                                                        
  do over qm ;                                                                                                                                                                                                                                                  
    qm = Q * (_i_ = M) ;                                                                                                                                                                                                                                        
  end ;                                                                                                                                                                                                                                                         
run ;    

Or, if you don't mind having missing values instead of zeroes, it's even simpler:

data want ;                                                                                                                                                                                                                                                     
  set have ;                                                                                                                                                                                                                                                    
  array qm_ [2] ;                                                                                                                                                                                                                                               
  qm_[M] = Q ;                                                                                                                                                                                                                                                  
run ; 

Kind regards

Paul D.