topic Re: Error renaming variables in a do loop in SAS Programming

Error renaming variables in a do loop

Jason2020 — Wed, 19 Dec 2018 21:33:55 GMT

Hi all,

I have a data set with several variables (more than 1000) that are numbers but saved as character variables. I would like to change their format to numeric. For example, the run below is for variables that have single digit numbers. I am getting the following error when I use arrays (but not when I do the run without array). Please let me know what I am doing wrong. Thanks.

Data lib.tmp2;

Set tmp1;

ARRAY B (30) B1-B30;

ARRAY C (30) C1-C30;

DO i=1 TO 30;

C(i)=input(B(i), 1.); drop B(i); rename C(i)=B(i);

END;

RUN;

ERROR 22-322: Syntax error, expecting one of the following: a name, -, :, ;, _ALL_, _CHARACTER_, _CHAR_, _NUMERIC_.

ERROR 22-322: Syntax error, expecting one of the following: -, :, =.

Re: Error renaming variables in a do loop

r_behata — Wed, 19 Dec 2018 21:54:16 GMT

try this :

data have;
input b1 $ b2 $ b3 $;
cards;
1 4 7
2 5 8
3 6 9
run; 

data want;
	set have;
	array a(3) b1-b3;
	array c(3) c1-c3;

	do i=1 to dim(a);
		c[i]=input(a[i],$2.);
	end;
run;

Re: Error renaming variables in a do loop

SuryaKiran — Wed, 19 Dec 2018 22:03:18 GMT

Hello,

You can make use of Dictionary.Columns and reconstruct the rename, drop and conversion.

data have;
b1='1';b2='2';b3='3';
run;


proc sql;
select  cats(name,'_'), /* Drop variables */
		cats(name,'=',name,'_'), /* Rename variables */
		cats(name,'=input(',name,'_',',1.);') /* Convert to Numeric */
	into:Drop separated by ' ', :rename separated by ' ',:Convert_num  separated by ' '
from dictionary.columns
where libname='WORK' and memname='HAVE'
;
quit;

%put "&rename";
%put "&Convert_num";
%put "&Drop";

data want(drop=&drop);
set have(rename=(&rename));
&convert_num;
run;

Re: Error renaming variables in a do loop

ShiroAmada — Thu, 20 Dec 2018 00:42:28 GMT

Try this....

data HAVE;
  input B1 $3.;
CARDS;
1
2
3
4
5
;
run;

DATA HAVE;
 SET HAVE;
 B2=B1;
 B3=B1;
run;

data MYVARSLIST;
  infile datalines dlm="|";
  input 
  var: $32.
  renvar: $32.;
datalines;
B1|C1
B2|C2
B3|C3
;
run;

proc sql noprint;
  select catt(count(*)) into :varn from MYVARSLIST;
  select var, renvar into :var1-:var&varn, :renvar1-:renvar&varn
  from MYVARSLIST;
quit;


%put &varn.;

%macro test;
data want;
 set have;

  %do i=1 %to &varn.;
    &&renvar&i=input(&&var&i.,3.);
    drop &&var&i.;
	rename &&renvar&i=&&var&i.;
  %end;

run;
%mend;
%test;

Re: Error renaming variables in a do loop

Jason2020 — Thu, 20 Dec 2018 02:09:47 GMT

This will note solve my issue. I have more than 1,400 variables and I want to keep the variable names. I therefore used the DROP and RENAME statements so that I can keep the same names of the variables. However, using these statements inside the do loop generates an error.
Using your example, here is what I want the program to do:
data have;
input b1 $ b2 $ b3 $;
cards;
1 4 7
2 5 8
3 6 9
run;

data want;
   set have;
   array b(3) b1-b3;
   array c(3) c1-c3;

   do i=1 to dim(b);
       c[i]=input(b[i],$2.);
       drop b(i);
        rename C(i)=b(i);
   end;
run;

Re: Error renaming variables in a do loop

r_behata — Thu, 20 Dec 2018 02:36:45 GMT

Let me know if this fulfills your requirement.

data have;
input b1 $ b2 $ b3 $;
cards;
1 4 7
2 5 8
3 6 9
run;

data want;
    set have;
    array b(3) b1-b3;
    array c(3) c1-c3;

    do i=1 to dim(b);
        c[i]=input(b[i],$2.);
       
     end;
     drop b: i;
run;


proc contents data=want out=temp(keep=name) nodetails noprint;
run;

proc sql noprint;
select cats(name,'=',tranwrd(name,'c','b')) into :ren_str separated by ' '
from temp;
quit;

%put &=ren_str;

proc datasets library=work nodetails noprint;
	modify want ;
	rename &ren_str;
run;

Re: Error renaming variables in a do loop

Tom — Thu, 20 Dec 2018 04:36:28 GMT

Note that the RENAME statement (or RENAME= dataset option) wants variable names. Using ARRAY references makes no sense because the RENAME operation is something that is calculated when the data step is complied. ARRAYs are a useful tool for dynamically referencing variables when the data step is running but cannot be used with RENAME statements.

Looks like want to change the TYPE of the variables. Unlike simply changing the display FORMAT attached to a variable to change its type you need to create new variables.

Here is a method that might make it easier. First transpose the character variables into a tall structure. Then convert the new single character variable into a number. You could then transpose it back. But perhaps you want to leave it in the tall structure instead.How are you planning to use 1,400 variables effectively anyway?

proc transpose data=have out=step1 ;
  by id ;
  var _character_;
run;

data step2;
  set step1 ;
  col2 = input(col1,32.);
run;

proc transpose data=step2 out=want;
  by id;
  var col2 ;
run;

Re: Error renaming variables in a do loop

s_lassen — Thu, 20 Dec 2018 13:20:27 GMT

The DROP and RENAME statements are not executable, they are declarations in the data step. Thus it makes no difference if you put them inside or outside a loop, and they cannot be used with arrays.

I would suggest creating a small macro to rename your variables, e.g.

%macro rename(prefix1,prefix2,from,to);
  %local i;
rename %do i=&from %to &to; &prefix1.&i=&prefix2.&i%end;
%mend;

Then your SAS program can be rewritten as

Data lib.tmp2;
  Set tmp1;
  ARRAY B (30) B1-B30;
  ARRAY C (30) C1-C30;
  DO i=1 TO 30;
    C(i)=input(B(i), 1.); 
  END;
  drop B1-B30;
  %rename(C,B,1,30);
RUN;

Re: Error renaming variables in a do loop

Jason2020 — Thu, 20 Dec 2018 15:47:07 GMT

Unfortunately, none of the proposed solutions worked for my data (possibly because I did not give enough details). However, all the suggestions were helpful. After some thinking, I solved the problem as follows. Thanks to all for your help.

data want1;

set have;

array b (30)

l1m l1md l1mm l2m l2md l2mm lc lcd lcm li lid lim lp lpd lpm

u1m u1md u1mm u2m u2md u2mm uc ucd ucm ui uid uim up upd upm;

array c (30) c1-c30;

do i=1 to dim(b);

c(i)=input(b[i], $2.);

end;

drop b: i;

run;

data want2;

set want1;

array b (30)

l1m l1md l1mm l2m l2md l2mm lc lcd lcm li lid lim lp lpd lpm

u1m u1md u1mm u2m u2md u2mm uc ucd ucm ui uid uim up upd upm;

array c (30) c1-c30;

do i=1 to dim(b);

b(i)=c[i];

end;

drop c: i;

run;