Convert all character variables to numeric and use the same variable names in the output data set

[Anita_n]

Dear all,

am tring to convert all character variables to numeric in my table. I saw a very nice post on SAS support. Which I think will perfectly work for me. My only problem is that I get an error message saying: 

ERROR: The length of the value of the macro variable RENAM_LIST (65540) exceeds the maximum
length (65534). The value has been truncated to 65534 characters.

proc sql noprint;
select trim(left(name)), trim(left(newname)),
trim(left(newname))||'='||trim(left(name))
into :c_list separated by ' ', :n_list separated by ' ',
:renam_list separated by ' '

from mytable;
quit;

I tried to use length= 65534 behind :renam_list separated by ' '

but it seems to be the wrong syntax. Is it any way possible to assign  lengths to c_list, n_list and renam_list here?

Thanks

[PaigeMiller]

The problem is that you are creating a macro variable, and macro variables anywhere in SAS cannot exceed a certain length of 65534 characters. This is not something you can change.

I had this problem once. I wound up using CALL EXECUTE and then there are no limits. You could also split dataset MYTABLE into smaller parts and then the SQL solution will work, but you will have to run it twice or more (once on each split).

[Anita_n]

Thanks for the hint:

What is the proper way of using call execute in this case?

[PaigeMiller]

Please provide a small portion of data set MYTABLE (2 or 3 rows ought to be fine)

[Anita_n]

NAME	TYPE	newname
group	2	group_n
asm_score	2	asm_score_n
kas_bsn	2	kas_bsn_n
kas_dsnr	2	kas_dsnr_n
kas_health_patient	2	kas_health_patient_n

[PaigeMiller]

@Anita_n 

where do you change the character variables to numeric variables in your process? I only see renaming.

[Tom]

You need to show how you are using those macro variables to get a fully worked solution, but in general you can just use a DATA step to generate the code directly instead of first making macro variables.   Since you were generating three lists you will probably need to process the dataset MYTABLE at least three times to generate all of the code, once for each place in the generated code where you were using the macro variable.

But here is a trick for generating a really long list into a macro variable.  What you can do is generate many shorter macro variables and generate one macro variable that references the others.  So for your example you might generate something like this:

c1=var1 var2
c2=var3 var4
n1=new1 new2
n2=new3 new4
r1=new1=var1 new2=var2
r2=new3=var3 new4=var4
c_list=&c1 &c2
n_list=&n1 &n2
renam_list=&r1 &r2

Now when you reference &C_LIST you will get var1 var2 var3 var4 .

Based on your SQL you could do use code something like this to generate those macro variables.

%let c_list=;
%let n_list=
%let renam_list=;

data _null_;
  row+1;
  length c n r $32000;
  do until(eof or length(r) > 30000);
    set mytable end=eof ;
    c=catx(' ',c,name);
    n=catx(' ',c,newname);
    r=catx(' ',catx('=',newname,name);
  end;
  call symputx(cats('c',row),c);
  call symputx(cats('n',row),n);
  call symputx(cats('r',row),r);
  call symputx('c_list',catx(' ',symget('c_list'),cats('&c',row)));
  call symputx('n_list',catx(' ',symget('n_list'),cats('&n',row)));
  call symputx('renam_list',catx(' ',symget('renam_list'),cats('&r',row)));
run;

[Anita_n]

data want;                                               
   set have;                                                 
   array ch(*) $ &c_list;                                    
   array nu(*) &n_list;                                      
   do i = 1 to dim(ch);                                      
      nu(i)=input(ch(i),8.);                                  
   end;                                                      
   drop i &c_list;                                           
   rename &renam_list;                                                                                      
run;             

[Tom]

So you only need one pass through the dataset to generate code that does that, but the code will look different. Instead of using array just generate separate assignment, rename and drop statements.

data want;                                               
   set have;   
new1=input(old1,32.);
rename new1=old1;
drop old1;
new2=input(old2,32.);
rename old2=new2;
drop old2;
....
run;          

So you might use code like this:

filename code temp;
data _null_;
  set mytable;
  file code;
  put name '=input(' oldname ',32.); rename ' name oldname '; drop ' oldname ';' ;
run;

data want;
  set have ;
%include code / source2;
run;

[Anita_n]

I get the following errors on running the code:

ERROR: Open code statement recursion detected.
1775
1776 data _null_;
1777 row+1;
---
180

ERROR 180-322: Statement is not valid or it is used out of proper order.

1778 length c n r $32000;
------
180

ERROR 180-322: Statement is not valid or it is used out of proper order.

[Tom]

---

@Anita_n wrote:

I get the following errors on running the code:

ERROR: Open code statement recursion detected.
1775
1776 data _null_;
1777 row+1;
---
180

ERROR 180-322: Statement is not valid or it is used out of proper order.

1778 length c n r $32000;
------
180

ERROR 180-322: Statement is not valid or it is used out of proper order.

---

Something before what you posted is the issue. Your truncated macro variables you generated before might have left your SAS session unstable.  Re-start SAS and try again.  If you get an error include more of the log so that it is possible to see the cause.

[PaigeMiller]

data mytable; /* Reconstruct mytable from input at SAS Communities */
infile cards missover;
input NAME & $18.	TYPE  	newname & $21.;
cards;
group	2	group_n
asm_score	2	asm_score_n
kas_bsn	 2	kas_bsn_n
kas_dsnr	2	kas_dsnr_n
kas_health_patient	2	kas_health_patient_n
;
data have; /* Example of actual data set */
    input (group asm_score kas_bsn kas_dsnr kas_health_patient)($);
    cards;
1 1 1 1 1
;
proc sql;
    select name,newname into :c_list separated by ' ',:n_list separated by ' ' from mytable;
quit;
%put &=c_list;
%put &=n_list; 

data _null_; /* Use CALL EXECUTE to create data set WANT from data set HAVE */
    set mytable end=eof;
    if _n_=1 then call execute('data want; set have;                                               
   array ch(*) $ &c_list;                                    
   array nu(*) &n_list;                                      
   do i = 1 to dim(ch);                                      
      nu(i)=input(ch(i),8.);                                  
   end;                                                      
   drop i &c_list;                                           
   rename ');                                                                             
    call execute(cats(newname,'=',name));
    if eof then call execute (';run;');
run;

[Tom]

That will reduce the likely hood of exceeding macro variable limit, but not eliminate it.

[PaigeMiller]

@Tom, yes, but in the original example, it was the longer macro variable @RENAM_LIST that was the problem, not the shorter macro variables. I suppose if the real example &C_LIST is too long then some other code would be needed. But doesn't your code suffer from the same limitation?

[Tom]

---

@PaigeMiller wrote:

@Tom, yes, but in the original example, it was the longer macro variable @RENAM_LIST that was the problem, not the shorter macro variables. I suppose if the real example &C_LIST is too long then some other code would be needed. But doesn't your code suffer from the same limitation?

---

The data step to generate the code I posted does not use macro variables at all.

The data step to generate multiple macro variables will hit a limit but it is much larger.  If we assume that the rename strings require 66 characters per rename pair (32*2 plus space and equal sign) we can fit 484 pairs into each macro variable R1,R2,....  We can reference at least 3000 macro variables in the 32K character limit for the generated RENAM_LIST macro variable. So it will work for at a minimum or 1.4 million pairs or variable names.