topic Re: Convert all character variables to numeric and use the same variable names in the output data se in SAS Programming

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

Anita_n — Thu, 18 Feb 2021 14:29:09 GMT

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

Re: Convert all character variables to numeric and use the same variable names in the output data se

PaigeMiller — Thu, 18 Feb 2021 14:29:36 GMT

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

Re: Convert all character variables to numeric and use the same variable names in the output data se

Anita_n — Thu, 18 Feb 2021 14:42:55 GMT

Thanks for the hint:

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

Re: Convert all character variables to numeric and use the same variable names in the output data se

PaigeMiller — Thu, 18 Feb 2021 14:48:27 GMT

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

Re: Convert all character variables to numeric and use the same variable names in the output data se

Anita_n — Thu, 18 Feb 2021 14:59:44 GMT

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

Re: Convert all character variables to numeric and use the same variable names in the output data se

Tom — Thu, 18 Feb 2021 15:14:29 GMT

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;

Re: Convert all character variables to numeric and use the same variable names in the output data se

PaigeMiller — Thu, 18 Feb 2021 15:12:52 GMT

@Anita_n

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

Re: Convert all character variables to numeric and use the same variable names in the output data se

Anita_n — Thu, 18 Feb 2021 15:16:53 GMT

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;

Re: Convert all character variables to numeric and use the same variable names in the output data se

Anita_n — Thu, 18 Feb 2021 15:19:52 GMT

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.

Re: Convert all character variables to numeric and use the same variable names in the output data se

Tom — Thu, 18 Feb 2021 15:21:48 GMT

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;

Re: Convert all character variables to numeric and use the same variable names in the output data se

Tom — Thu, 18 Feb 2021 15:25:25 GMT

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

Re: Convert all character variables to numeric and use the same variable names in the output data se

PaigeMiller — Thu, 18 Feb 2021 15:39:32 GMT

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;

Re: Convert all character variables to numeric and use the same variable names in the output data se

Tom — Thu, 18 Feb 2021 15:48:06 GMT

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

Re: Convert all character variables to numeric and use the same variable names in the output data se

PaigeMiller — Thu, 18 Feb 2021 15:57:16 GMT

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

Re: Convert all character variables to numeric and use the same variable names in the output data se

Tom — Thu, 18 Feb 2021 16:11:27 GMT

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

Re: Convert all character variables to numeric and use the same variable names in the output data se

Anita_n — Thu, 18 Feb 2021 16:15:28 GMT

I need to test the methods patiently to see which works for me. I will let you know then when am done.

Thankyou both for the help.

Re: Convert all character variables to numeric and use the same variable names in the output data se

ballardw — Thu, 18 Feb 2021 16:19:33 GMT

Maybe it is worth discussing prevention vs fixing.

WHY are all the variables you want as numeric currently character? If this was the result of an incorrect file import then perhaps the better method would be to correct the read step instead of fixing it afterwards.

Or possibly even EXPORT the data to a text formatted file and write a data step to read that back in with the desired properties.

One moderately frequent cause of variables that should be numeric coming into SAS as character is use of Proc Import with a file that uses some sort of text like NULL or NA for what should be missing values. That can be dealt with using a custom informat and data step to read the values the first time around.

Re: Convert all character variables to numeric and use the same variable names in the output data se

Anita_n — Thu, 18 Feb 2021 16:41:54 GMT

The data comes from Postgresql and contains 1634 variables and 1985 observations. The variable names are very long and have special characters. I have tried all means to use sas to import but no way.

I ended up using stattransfer to import this to sas. I realise that, all my num vars are converted to char. Dates to chars. For the issue with the dates I have been able to solve it. some decimal values like 4.2 are converted to 4.Feb

It isn't easy to edit this file, even though it's a delimited file (semicolon seperated)

the most tedious part are the long varnames

Re: Convert all character variables to numeric and use the same variable names in the output data se

Tom — Thu, 18 Feb 2021 17:17:57 GMT

You can connect SAS to Postgres. It will require at least SAS/Access to ODBC license.

If the variable names are longer than 32 bytes then you will have issues. Same with table (dataset) names. But you can work around that with pass thru code so they rename them on the remote side before pulling them into SAS.

If you have values being converted to dates then you have probably accidentally opened a delimited file with Excel, I doubt that STAT/Transfer would do that itself. Do NOT let EXCEL automatically open a delimited file. If you do open it with Excel make sure to do it under control so that you can tell it how to treat each column in the delimited file.

If you have a delimited file then read it directly with SAS data step. Then you have total control over the variable names, type and length. You can control how the text in the file is converted to the values stored in the SAS variables. You can control what SAS formats are attached the variables (if there is any need to attach any formats to any of the variables).

Re: Convert all character variables to numeric and use the same variable names in the output data se

Anita_n — Thu, 18 Feb 2021 17:31:13 GMT

Yes, you are right but in my case the data step does work properly because of the problem with the variables. I cannot connect directly to postgresql because the data is from an external body who works with postgresql. I receive just the delimited file from them