Hundreds of variables: convert character to numeric

[art297]

@sovrappensiero: The SQL part is simply building code for the data step that follows it.

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

The first catt in the select section is building a space separated list to recode. I.e., given var1 and var2, e.g., it is creating:

var1=_var1 var2=_var2, and putting the result in a macro variable called &rens.

The second catt is building a set of statements that will take advantage of those recodes. i.e., given var1 and var2, e.g., it is creating var1=input(_var1,8.);

var2=input(_var2,8.);

and putting the result in a macro variable called called &vars.

The data step that follows simply uses the two macro variables as code to recode the variables and, for the latter, statements that will accomplish the actual conversion from character to numeric.

Art, CEO, AnalystFinder.com

[sovrappensiero]

Thanks so much for taking the time to explain that part. I got it now. I'm so pleased that I bothered to ask the initial question that seemed rather mundane at first (mundane although I couldn't solve it LOL). I've learned a lot since Thursday evening.

[mansour_ib_sas]

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;

[mansour_ib_sas]

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;

[Ksharp]

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;

[Ksharp]

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;

[sovrappensiero]

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!

[art297]

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