topic Re: Use do loop index in new variable name in SAS Programming

Use do loop index in new variable name

MarkusB — Wed, 29 Jan 2020 16:34:42 GMT

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!

Re: Use do loop index in new variable name

novinosrin — Wed, 29 Jan 2020 16:39:53 GMT

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

Re: Use do loop index in new variable name

andreas_lds — Wed, 29 Jan 2020 16:44:35 GMT

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

Re: Use do loop index in new variable name

hashman — Wed, 29 Jan 2020 18:55:35 GMT

@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.

Re: Use do loop index in new variable name

MarkusB — Thu, 30 Jan 2020 10:09:58 GMT

This works - thank you all for your replies!