topic Re: Hundreds of variables: convert character to numeric in SAS Procedures

Hundreds of variables: convert character to numeric

sovrappensiero — Thu, 30 Aug 2018 21:57:04 GMT

Hi!

I have a dataset with hundreds (on the order of 400-500) of indicator variables (values 1,2, or 3) that I imported from Excel (.xlsx) and they have imported as character values. I need them to be numeric. If relevant, the data were transposed after importing (initial data look like mixed data type - see below for an example). They are not all clustered together, so I cannot use something like array cha(*) var1 -- var450 ...

Is there any way to convert hundreds of "truly numeric" variables that are masquerading as character variables, and are scattered among legitimate character variables, in any way that avoids me having to list all of them or convert them one-by-one?

Original data imported:

City1 City2 City3 City4 City5

Var1 words words words words words

Var2 1 2 1 2 2

Var3 2 2 1 1 1

Var4 words words words words words

Var5 words words words words words

After transposing:

Var1 Var2 Var3 Var4 Var5

City1 words 1 2 words words

City2 words 2 2 words words

City3 words 1 1 words words

City4 words 2 1 words words

City5 words 2 1 words words

I'd love some suggestions on how to fix this! I know how to do this easily in R, perl, bash, and other programming languages, but at the moment I am constrained to using SAS.

Re: Hundreds of variables: convert character to numeric

Reeza — Thu, 30 Aug 2018 22:07:19 GMT

Can you use this notation which would select all character variables between first and last variable?

array to_convert(*) first_var -character- last_var;

@sovrappensiero wrote:

Hi!

I have a dataset with hundreds (on the order of 400-500) of indicator variables (values 1,2, or 3) that I imported from Excel (.xlsx) and they have imported as character values. I need them to be numeric. If relevant, the data were transposed after importing (initial data look like mixed data type - see below for an example). They are not all clustered together, so I cannot use something like array cha(*) var1 -- var450 ...

Is there any way to convert hundreds of "truly numeric" variables that are masquerading as character variables, and are scattered among legitimate character variables, in any way that avoids me having to list all of them or convert them one-by-one?

Original data imported:

             City1      City2      City3     City4      City5

Var1     words      words    words     words      words

Var2       1              2           1             2             2

Var3       2              2           1             1             1

Var4     words     words    words     words      words

Var5     words      words    words     words      words

After transposing:

            Var1      Var2      Var3     Var4      Var5

City1     words     1           2     words      words

City2     words    2           2     words      words

City3     words     1           1     words      words

City4     words     2           1     words      words

City5     words     2           1     words      words

I'd love some suggestions on how to fix this! I know how to do this easily in R, perl, bash, and other programming languages, but at the moment I am constrained to using SAS.

Re: Hundreds of variables: convert character to numeric

ballardw — Thu, 30 Aug 2018 22:09:17 GMT

@sovrappensiero wrote:

Hi!

I have a dataset with hundreds (on the order of 400-500) of indicator variables (values 1,2, or 3) that I imported from Excel (.xlsx) and they have imported as character values. I need them to be numeric. If relevant, the data were transposed after importing (initial data look like mixed data type - see below for an example). They are not all clustered together, so I cannot use something like array cha(*) var1 -- var450 ...

Easiest may be to go back to the IMPORT stage.

First save the Excel file to a CSV file. (Reason: there are options available for delimited files not available for Excel).

Import the CSV.

If using Proc Import code then add the option GUESSINGROWS=MAX. If using a wizard find the options and set the "number of rows to guess" to a very large number, ideally the number of rows in the source file. Also indicate which row of data contains the first actual data row. A common cause of problems when there is one or more blank header rows. With Excel you can't tell SAS to ignore them. With a CSV file you can indicate the first row of data with the wizard or the DATAROW statement in Proc Import code.

Be default the import procedure guesses as to variable type using a very few rows of data. If the full range of values are not encountered in the first rows then the procedure guesses wrong.

Re: Hundreds of variables: convert character to numeric

sovrappensiero — Thu, 30 Aug 2018 22:12:31 GMT

Hi Reeza! I can’t do this because ALL the variables of of character type.

Re: Hundreds of variables: convert character to numeric

Reeza — Thu, 30 Aug 2018 22:15:00 GMT

Ok, so if you know how to do this in R, how would you identify which variables in R would need to be converted?

Re: Hundreds of variables: convert character to numeric

sovrappensiero — Thu, 30 Aug 2018 22:15:06 GMT

Thank you I will try to import it this way!

Re: Hundreds of variables: convert character to numeric

sovrappensiero — Thu, 30 Aug 2018 22:26:35 GMT

Good question. Firstly, I think they would automatically import as numeric in R. If not, I could re-arrange the columns so that all the numeric ones are together(a real pain, but I searched for how to do this in SAS and I couldn’t find a way although by that time I was a little tired and hungry, LOL). Then I could use an apply function to convert all the variables from column 1 to whatever using the as.numeric() function. I ran into a problem with the array in SAS for two reasons:

1. When SAS cannot concert a legitimate character variable to numeric, instead of ignoring it and throwing a warning it converts it to missing. So I cannot use _ALL_ and apply a conversion function hoping that it would “smart convert” only things that look like numbers go numeric format.

2. Because my variables are scattered, there is no easy way to rename them. SAS requires you that, for example newvar = input(oldvar, best8.). In R you can change variables on the fly.

Re: Hundreds of variables: convert character to numeric

sovrappensiero — Thu, 30 Aug 2018 22:27:40 GMT

I’m sorry for the typos! I was using my phone and typing faster than the phone can input letters. 😊

Re: Hundreds of variables: convert character to numeric

Reeza — Thu, 30 Aug 2018 22:32:35 GMT

Unfortunately, given the data structure - in rows rather than columns I don't think simply converting the file to CSV would help.

Except if you take the current data, export it to CSV and then read it back in as numeric. But you'd have to specify the character types anyways and if you're going to do that anyways, you may as well clean it up in SAS.

I would be happy to be wrong though 🙂

@sovrappensiero wrote:
Thank you I will try to import it this way!

Re: Hundreds of variables: convert character to numeric

Reeza — Thu, 30 Aug 2018 22:45:22 GMT

@sovrappensiero wrote:
Good question. Firstly, I think they would automatically import as numeric in R. If not, I could re-arrange the columns so that all the numeric ones are together(a real pain, but I searched for how to do this in SAS and I couldn’t find a way although by that time I was a little tired and hungry, LOL). Then I could use an apply function to convert all the variables from column 1 to whatever using the as.numeric() function. I ran into a problem with the array in SAS for two reasons:

Assuming the first data set is what you had in Excel, I don't think it would in R if your data is in mixed types as shown. Even R forces types on columns not along rows.

@sovrappensiero wrote:

1. When SAS cannot concert a legitimate character variable to numeric, instead of ignoring it and throwing a warning it converts it to missing. So I cannot use _ALL_ and apply a conversion function hoping that it would “smart convert” only things that look like numbers go numeric format.

You can test this using ANYDIGIT or ANYALPHA and then applying the conversion conditionally.

@sovrappensiero wrote:

2. Because my variables are scattered, there is no easy way to rename them. SAS requires you that, for example newvar = input(oldvar, best8.). In R you can change variables on the fly.

Unfortunately, this is definitely an issue that SAS needs to resolve. One way is to add a prefix to all automatically using the SASHELP.VCOLUMN table. Then you can keep track of which ones you need to convert and rename after the fact or delete all variables that are all missing. This is definitely not an easy, beginner level process though.

If you're doing this one time, I would probably copy and transpose the data in Excel and then re-import into SAS using GUESSINGROWS=MAX as suggested earlier.

If this needs to be repeated, that's obviously not a good solution!

Re: Hundreds of variables: convert character to numeric

ballardw — Thu, 30 Aug 2018 23:01:26 GMT

@sovrappensiero wrote:

Hi!

I have a dataset with hundreds (on the order of 400-500) of indicator variables (values 1,2, or 3) that I imported from Excel (.xlsx) and they have imported as character values. I need them to be numeric. If relevant, the data were transposed after importing (initial data look like mixed data type - see below for an example). They are not all clustered together, so I cannot use something like array cha(*) var1 -- var450 ...

Is there any way to convert hundreds of "truly numeric" variables that are masquerading as character variables, and are scattered among legitimate character variables, in any way that avoids me having to list all of them or convert them one-by-one?

Original data imported:

             City1      City2      City3     City4      City5

Var1     words      words    words     words      words

Var2       1              2           1             2             2

Var3       2              2           1             1             1

Var4     words     words    words     words      words

Var5     words      words    words     words      words

After transposing:

            Var1      Var2      Var3     Var4      Var5

City1     words     1           2     words      words

City2     words    2           2     words      words

City3     words     1           1     words      words

City4     words     2           1     words      words

City5     words     2           1     words      words

I'd love some suggestions on how to fix this! I know how to do this easily in R, perl, bash, and other programming languages, but at the moment I am constrained to using SAS.

Since the data structure wasn't included the previous comments I made won't work.

However, have you ever used the EXCEL Copy>Paste Special>Transpose feature? Highlight all the data, copy, go to a new sheet or work book and select the Paste Special and Transpose. Save the result as CSV and import.

One hopes that you do not have lots of files of this type.

Re: Hundreds of variables: convert character to numeric

Tom — Fri, 31 Aug 2018 00:21:17 GMT

One possibility is to just dump your current (converted) dataset to a delimited file and then let PROC IMPORT figure out which variables are numeric.

filename csv temp;
proc export data=have outfile=csv dbms=csv ; run;
proc import datafile=csv out=want dbms=csv; run;

Otherwise If your existing conversion process was a classic double transpose then you could try analyzing the middle (tall skinny) version of the data to find out the names of the variables whose set of values only include '1','2' and '3' and use that list in your char to num conversion step.

Re: Hundreds of variables: convert character to numeric

art297 — Fri, 31 Aug 2018 00:29:43 GMT

I think that the following will do what you want:

data have;
  input (City Var1 x y z Var5) ($);               
  cards;
City1     words     1           2     words      words                            
City2     2     2           2     words      words                                   
City3     words     1           .     words      words                                   
City4     words     2           1     words      words              
City5     words     2           1     words      words              
;

proc format; 
   value $allnum 
         other=[anyalpha()];
run;

ods output onewayfreqs=work.owf;
proc freq data=have;
  format var1--var5 $allnum.;
  tables var1--var5/ missing;
run;
ods listing;

data owf;
  length var $32 fvalue $50 ;
  set owf;
  by table notsorted;
  if first.table and last.table;
  var=scan(table,-1);
  fvalue=vvaluex('F_'||var);
  if fvalue eq 0;
  keep var;
run;

proc sql noprint;
  select catt(var,'=_',var),
         catt(var,'=input(_',var,',8.);')
    into :rens separated by ' ',
         :vars separated by ' '
      from owf
  ;
quit;

data want (drop=_:);
  set have (rename=(&rens.));
  &vars.;
run;

Art, CEO, AnalystFinder.com

Re: Hundreds of variables: convert character to numeric

mansour_ib_sas — Fri, 31 Aug 2018 12:45:36 GMT

Hello,

I propose this solution:

data test;
input (varr City1      City2      City3     City4      City5) ($);
cards;
Var1     words      words    words     words      words

Var2       1              2           1             .             2

Var3       2              2           1             1             1

Var4     words      words    words     words      words

Var5     words      words    words     words      words
;run;


data test1;
set test;
array nomm(*)   city1-character-city5;
do i=1 to dim(nomm);
if prxmatch("/[^a-zA-Z]/", nomm(i))=1 then varnum=1;
else varnum=0;
end; 
run;


data test2 test3;
set test1; 
if varnum=1 then output test2;
if varnum=0 then output test3;
run;


proc sql;
select catt('_',name) into : varname separated by ' ' from sashelp.vcolumn 
where memname='TEST22';
quit;


proc transpose data =test2 out=test22(drop=_name_); var  city1-city5; id varr; run;

data test22(drop=var: i);
set test22;
array varr(*) var:;
array varnum(*) &varname;
do i=1 to dim(varr);
varnum(i)=input(varr(i),3.);
end;
run;


%macro rena();
data test22;
set test22;
     rename 
                  %do i = 1 %to %sysfunc(countw(&varname));

                  %scan(&varname.,&i.) = %substr(%scan(&varname.,&i.),2)

                  %end;;

run;

%mend;
%rena;


proc transpose data =test3 out=test33(drop=_name_); var  city1-city5; id varr; run;

data finale;
retain var1-var5;
merge test22 test33;
run;

Re: Hundreds of variables: convert character to numeric

mansour_ib_sas — Fri, 31 Aug 2018 14:26:34 GMT

I made some corrections to the previous solution. sorry

data test;
input (varr City1      City2      City3     City4      City5) ($);
cards;
Var1     words      words    words     words      words

Var2       1              2           m          .             2

Var3       4             4         p          1             1

Var4     words      words    words     words      words

Var5     words      words    words     words      words
;run;


data test1;
set test;
array nomm(*)   city1-character-city5;
do i=1 to dim(nomm);
if prxmatch("/[^a-zA-Z]/", nomm(i))=1 then varnum=1;
else varnum=0;
output;
end; 
run;



proc sql;
select strip(quote(t.varr)) into : varnamea separated by ' ' from 
 (select varr from test1 group by varr having sum(varnum)=5) as t;

select catt('_',t.varr) into : varnameb separated by ' ' from 
 (select varr from test1 group by varr having sum(varnum)=5) as t;

select strip(quote(t.varr)) into : varnamea1 separated by ',' from 
 (select varr from test1 group by varr having sum(varnum) ne 5) as t;

select catt('_',t.varr) into : varnameb1 separated by ' ' from 
 (select varr from test1 group by varr having sum(varnum) ne 5) as t;
quit;


%macro tt;

%if %symexist(&varnamea) %then %do;
proc sql;
create table test2 as select * from test where varr in (&varnamea);

create table test3 as select * from test where varr in (&varnamea1);
quit;


proc transpose data =test2 out=test22(drop=_name_); var city1-city5 ; id varr; run;

data test22(drop=var: i);
set test22;
array varr(*) var:;
array varnum(*) &varnameb;
do i=1 to dim(varr);
varnum(i)=input(varr(i),3.);
end;
run;


%macro rena();
data test22;
set test22;
     rename 
                  %do i = 1 %to %sysfunc(countw(&varnameb.));

                  %scan(&varnameb.,&i.) = %substr(%scan(&varnameb.,&i.),2)

                  %end;;

run;

%mend;
%rena;


proc transpose data =test3 out=test33(drop=_name_); var  city1-city5; id varr; run;

data finale;
retain var1-var5;
merge test22 test33;
run;


%end;
%else %do;
%put "!!!!!!!!!!!! All variable are characters!!!!!!!";

data finale;
set test;
run;

%end;
%mend  tt;

%tt;



%symdel varnamea varnamea1 varnameb varnameb1;

Re: Hundreds of variables: convert character to numeric

Ksharp — Fri, 31 Aug 2018 14:34:33 GMT

data test;
input (var City1      City2      City3     City4      City5) ($);
cards;
Var1     words      words    words     words      words
Var2       1              2           1             .             2
Var3       2              2           1             1             1
Var4     words      words    words     words      words
Var5     words      words    words     words      words
;
run;

proc transpose data=test out=temp;
id var;
var _all_;
run;
data vname;
 set temp;
 array x{*} $ _all_;
 do i=1 to dim(x);
   if anydigit(strip(x{i}))=1 then do;vname=vname(x{i});output;end;
 end;
 keep vname;
run;
proc sort data=vname nodupkey;
by vname;
run;
data _null_;
 set vname end=last;
 if _n_=1 then call execute(catt('data want;set temp(firstobs=2);'));
 call execute(catt('new_',vname,'=input(',vname,',best32.);'));
 if last then call execute('run;');
run;
data _null_;
 set vname end=last;
if _n_=1 then call execute('data want;set want; drop ');
 call execute(vname);
if last then call execute(';run;');
run;

Re: Hundreds of variables: convert character to numeric

Ksharp — Fri, 31 Aug 2018 14:36:04 GMT

data test;
input (var City1      City2      City3     City4      City5) ($);
cards;
Var1     words      words    words     words      words
Var2       1              2           1             .             2
Var3       2              2           1             1             1
Var4     words      words    words     words      words
Var5     words      words    words     words      words
;
run;

proc transpose data=test out=temp;
id var;
var _all_;
run;
data vname;
 set temp;
 array x{*} $ _all_;
 do i=1 to dim(x);
   if anydigit(strip(x{i}))=1 then do;vname=vname(x{i});output;end;
 end;
 keep vname;
run;
proc sort data=vname nodupkey;
by vname;
run;
data _null_;
 set vname end=last;
 if _n_=1 then call execute(catt('data want;set temp(firstobs=2);'));
 call execute(catt('new_',vname,'=input(',vname,',best32.);'));
 if last then call execute('run;');
run;
data _null_;
 set vname end=last;
if _n_=1 then call execute('data want;set want; drop ');
 call execute(vname);
if last then call execute(';run;');
run;

Re: Hundreds of variables: convert character to numeric

sovrappensiero — Fri, 31 Aug 2018 15:00:01 GMT

Goodness! I never imagined there would be so many complex solutions to this problem! Thank you all so much for taking time to help me. It's going to take me awhile to fully understand all these proposed solutions and select one to apply in my case, and first I have to get through my work day. I like the simplicity of the ones that involve exporting and re-importing, but I'll definitely focus on the others. This is not just a one-time problem, unfortunately. Data comes in like this quite often so I'll be looking for a solution that fits most elegantly into a data cleaning pipeline.

This is my first time using SAS Communities. (My background is that I used SAS extensively as an epidemiologist for several years up until about 2013, but since then I have switched to bioinformatics where I mostly use other software/languages. I still tutor SAS, and this problem is so far the first that has stumped me. Found the forums and off we go on a learning adventure!) I'm used to Stack Overflow...can I accept multiple solutions here? What are my options when several people have proposed functional solutions? I want to follow the rules and preferences of this community...

Thanks for giving me tons of fodder for my brain, and for helping me tackle this issue!

Re: Hundreds of variables: convert character to numeric

art297 — Fri, 31 Aug 2018 15:50:29 GMT

Unfortunately you can only select one correct solution. However, there aren't any community rules regarding which one you select .. i.e., the choice is yours. You can always 'like' as many responses as you care to mark that way, but only one can be marked as being 'correct'.

Art, CEO, AnalystFinder.com

Re: Hundreds of variables: convert character to numeric

s_lassen — Tue, 04 Sep 2018 08:07:35 GMT

Something like this may work (using the test data supplied by @Ksharp😞

data test;
input (varr City1      City2      City3     City4      City5) ($);
cards;
Var1     words      words    words     words      words
Var2       1              2           1             .             2
Var3       2              2           1             1             1
Var4     words      words    words     words      words
Var5     words      words    words     words      words
;run;

First, transpose:

proc transpose data=test out=trans;
  var city:;
  id  varr;
run;

Then create a temporary program file to convert the numeric variables:

filename tempsas temp;
data _null_;
  set test;
  file tempsas;
  array test(*) City:;
  do _N_=1 to dim(test);
    if input(test(_N_),?? best32.)=. and test(_N_) not in('.',' ') then
      delete;
    end;
  put
   '_' varr '=input(' varr ',best32.);' /
   'drop ' varr ';' /
   'rename _' varr '=' varr ';'
   ;
run;

Then use that to convert the numeric variables:

data want;
  set trans;
  %include tempsas/source2;
run;