Error renaming variables in a do loop

[Jason2020]

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: -, :, =.

[r_behata]

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;

[Jason2020]

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;

[r_behata]

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;

[Jason2020 · Accepted solution]

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;

[SuryaKiran]

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;

[ShiroAmada]

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;

[Tom]

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;

[s_lassen]

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;