Create new variables using and based on arrays

[mgrasmussen]

Dear SAS experts

I would like to create new variables which are modified versions of existing variables. I would like the new variables to have a very similar names to those already present. Adding a simple suffix, e.g. "_r", would be fine. Because I want to perform this operation many times (many variables, all numerical) I would like to do it using an array code. However, I cannot get it to work (desired variable: want_var). Here is a very simple example with only one variable of what I am trying to accomplish. Can someone suggest a modification to the array code? Thanks. I realize that the array code is not logical for only variable, but it is just meant as an example.

data have;
input have_var want_var;
datalines;
. .
1 5
2 .
8 .
3 .
. .
7 .
2 .
1 5
. .
;
run;

data have (drop=i);
set have;
array modify have_var;
do i=1 to dim(modify);
if modify(i)=1 then modify(i)_r=5;
end;
run;

Log:

1 ;*';*";*/;quit;run;
2 OPTIONS PAGENO=MIN;
3 %LET _CLIENTTASKLABEL='Program';
4 %LET _CLIENTPROCESSFLOWNAME='Process Flow';
5 %LET _CLIENTPROJECTPATH='';
6 %LET _CLIENTPROJECTPATHHOST='';
7 %LET _CLIENTPROJECTNAME='';
8 %LET _SASPROGRAMFILE='';
9 %LET _SASPROGRAMFILEHOST='';
10
11 ODS _ALL_ CLOSE;
12 OPTIONS DEV=PNG;
13 GOPTIONS XPIXELS=0 YPIXELS=0;
14 FILENAME EGSR TEMP;
15 ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
16 STYLE=HtmlBlue
17 STYLESHEET=(URL="file:///C:/Program%20Files/SASHome/SASEnterpriseGuide/7.1/Styles/HtmlBlue.css")
18 NOGTITLE
19 NOGFOOTNOTE
20 GPATH=&sasworklocation
SYMBOLGEN: Macro variable SASWORKLOCATION resolves to "E:\Work\_TD158488_SRVESBAPPSAS34V_\Prc2/"
21 ENCODING=UTF8
22 options(rolap="on")
23 ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
24
25 GOPTIONS ACCESSIBLE;
26 data have (drop=i);
27 set have;
28 array modify have_var;
29 do i=1 to dim(modify);
30 if modify(i)=1 then modify(i)_r=5;
__
22
ERROR 22-322: Syntax error, expecting one of the following: +, =.

31 end;
32 run;

NOTE: The SAS System stopped processing this step because of errors.
MVA_DSIO.OPEN_CLOSE| _DISARM| STOP| _DISARM| 2021-11-04T10:22:54,259+01:00| _DISARM| WorkspaceServer| _DISARM| | _DISARM| |
_DISARM| | _DISARM| 21860352| _DISARM| 10| _DISARM| 18| _DISARM| 3473| _DISARM| 9042502| _DISARM| | _DISARM| | _DISARM| | _DISARM|
| _DISARM| | _DISARM| | _ENDDISARM
WARNING: The data set WORK.HAVE may be incomplete. When this step was stopped there were 0 observations and 3 variables.
MVA_DSIO.OPEN_CLOSE| _DISARM| STOP| _DISARM| 2021-11-04T10:22:54,259+01:00| _DISARM| WorkspaceServer| _DISARM| | _DISARM| |
_DISARM| | _DISARM| 21860352| _DISARM| 10| _DISARM| 18| _DISARM| 3997| _DISARM| 9045246| _DISARM| | _DISARM| | _DISARM| | _DISARM|
| _DISARM| | _DISARM| | _ENDDISARM
WARNING: Data set WORK.HAVE was not replaced because this step was stopped.
PROCEDURE| _DISARM| STOP| _DISARM| 2021-11-04T10:22:54,259+01:00| _DISARM| WorkspaceServer| _DISARM| | _DISARM| | _DISARM|
| _DISARM| 21860352| _DISARM| 10| _DISARM| 18| _DISARM| 140597| _DISARM| 9047432| _DISARM| | _DISARM| | _DISARM| | _DISARM| |
_DISARM| | _DISARM| | _ENDDISARM
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds

33
34 GOPTIONS NOACCESSIBLE;
2 The SAS System 10:22 Thursday, November 4, 2021

35 %LET _CLIENTTASKLABEL=;
36 %LET _CLIENTPROCESSFLOWNAME=;
37 %LET _CLIENTPROJECTPATH=;
38 %LET _CLIENTPROJECTPATHHOST=;
39 %LET _CLIENTPROJECTNAME=;
40 %LET _SASPROGRAMFILE=;
41 %LET _SASPROGRAMFILEHOST=;
42
43 ;*';*";*/;quit;run;
44 ODS _ALL_ CLOSE;
45
46
47 QUIT; RUN;
48

[Kurt_Bremser · Accepted solution]

Transpose to long, create the new variable, transpose back to wide, and merge with the existing dataset (unless you find that the long layout will be more useful anyway).

See this example code:

data have;
input id $ var1 var2;
datalines;
X 1 2
Y 4 5
;

proc transpose
  data=have
  out=long (rename=(col1=var))
;
by id;
var var:;
run;

data want_long;
set long;
var_r = var * 5; /* or whatever you intend to do */
run;

proc transpose
  data=want_long
  out=want_wide (drop=_name_)
  prefix=var_r
;
by id;
var var_r;
run;

data want;
merge
  have
  want_wide
;
by id;
run;

[mgrasmussen]

Dear Kurt

Thanks. I was hoping it could be done in a more simple way though.

[Kurt_Bremser]

To create new variables dependent on the number of variables already existing, you have to first determine the number, as this needs to be known before the data step is compiled. Arrays can't be used, as the DIMI() function is a runtime function.

An alternative would be to read the variables from dictionary.columns and create the data step code from that, either with CALL EXECUTE or writing to an include file.

And, as always, whenever there arises the need to work with a series of variables, contemplate switching to a long layout, which is always easier to code for.

[s_lassen]

You cannot dynamically create new variables in a data step. The code is compiled once.

So you will have to get the names of the variables you need first.

Give data like this:

data have;      
  input x y z p;
cards;          
1 5 2 65        
46 1 43 65      
2 3 4 1         
;run;

You can get the names of the new variables that you want into a macro variable like this:

data _null_;                                               
  set have;                                                
  length newnames $2000;                                   
  array modify(*) x--p;                                    
  do _N_=1 to dim(modify);                                 
    call catx(' ',newnames,cats(vname(modify(_N_)),'_r')); 
    end;                                                   
  call symputx('newvars',newnames);                        
  stop;                                                    
run;                                                       
%put &newvars;

Then it is relatively simple to do the rest:

data want;                    
  set have;                   
  array modify(*) x--p;       
  array newvars(*) 8 &newvars;
  do _N_=1 to dim(modify);    
    if modify(_N_)=1 then     
      newvars(_N_)=5;         
    end;                      
run;

[AMSAS]

I'm not certain I understand the problem fully, but if you just want to do something like this:

data want ;
  set have ;
    myVar1_r=myVar1 ;
    myVar2_r=myVar2 ;
    ..
    myVar<n>_r=myVar<n> ;
run ;
  

Then this will do it

options mlogic mprint symbolgen ;

/* Create sample data */
data have ;
	infile cards ;
	input myVar1 myVar2 myVar3 ;
cards ;
1 2 3
4 5 6
7 8 9
;

/* Get variables we are interested in from SASHELP.VCOLUMN */
data _null_ ;
	set sashelp.vcolumn 
		(where =(
			libname="WORK" and 
			memname="HAVE" and
			name ? "myVar" )) ;
	/* Create macro variables var<n> that will contain an assignment statement 
       e.g. 
			myVar1_r=myVar1 ;
			myVar2_r=myVar2 ;
			...
			myVar<n>_r=myVar<n> ;
	*/
	call symput("var"!!left(putn(_n_,"8.")),trim(name)!!"_r="!!name!!" ;") ;
	/* Store number of variables we are assigning */
	call symput("varCnt",putn(_n_,"8.")) ;
run ;

/* Macro to assign new variables */
%macro rename(cnt) ;
	/* Create want dataset */
	data want ;
		/* Read have dataset */
		set have ;

		/* loop through the macro variables created in step above */
		%do i=1 %to &cnt ;
			/* debugging %put statement */
			%put var&i : &&var&i ;
			/* resolve the macro variables created in step above 
				e.g. 
			    	myVar1_r=myVar1 ;
					myVar2_r=myVar2 ;
					...
					myVar<n>_r=myVar<n> ;
			*/
			&&var&i ;
		%end ;
	run ;
%mend ;

/* Call the macro, passing number of variables to rename */
%rename(&varCnt) ;