BookmarkSubscribeRSS Feed
ginak
Quartz | Level 8

Hello,


I have a huge data set with 134 variables. Even though everything is a number, they're all listed as character variables. I want to convert everything to numeric variables without having to write 134 input (var name, blah);  statements or 134 newvar=oldcharvar*1 statements. I am guessing the fastest way to do this is either using an array or a macro loop. But with an array, don't the variables have to have a similar variable name? like temp1 temp2 temp3 etc. Mine are all different.


for example, BIRTH_YEAR is a character variable, even though everything listed is a number (1956,1976, etc). and same with AGE.

Help? Thanks,
Gina

15 REPLIES 15
Haikuo
Onyx | Level 15

Your guts are telling the truth: array() is the way to go:

data have;

input (a x1 _3) ($);

cards;

13413 1434 1344

;

/*this sql part is unnecessary if you don't care about the new names being related to the old ones*/

proc sql noprint;

  select cats('new_',name) into :name separated by ' ' from dictionary.columns where libname='WORK' AND MEMNAME='HAVE'; QUIT;

data want;

set have;

array old $ a--_3;

array new &name;

do i=1 to dim(old);

   new(i)=input(old(i),best.);

end;

keep &name;

run;

Haikuo

Ksharp
Super User

Less code with:

data have;
input (a x1 _3) ($);
cards;
13413 1434 1344
;
run;
proc sql noprint;
  select cats('new_',name,'=',name,';') into :name separated by ' ' from dictionary.columns where libname='WORK' AND MEMNAME='HAVE'; QUIT;
data want(keep=new_:); 
 set have;
 &name
run;

Ksharp

Haikuo
Onyx | Level 15

While your code is slick, but it does not deliver what OP asked for: char to num conversion. Minor tweak is need:

proc sql noprint;

  select cats('new_',name,'=',name,'+0',';') into :name separated by ' ' from dictionary.columns where libname='WORK' AND MEMNAME='HAVE'; QUIT;

of course you can also cats in 'input' or some other ways.

Haikuo

Ksharp
Super User

Hi. Bian Hai Kuo ,

There are no need to add " +0 " . Defaultly ,sas will set a variable be a numeric type if you don't specify it .

So SAS will automatically convert it from character to numeric. You can try my code again.

Haikuo
Onyx | Level 15

KeShan,

I have tried your code on both 9.2 and 9.3. They both came back with unchanged character. What you have done essentially is to assign  char values to new variable names, those char values, if not converted, will remain char.

Try it for yourself.

Haikuo

Ksharp
Super User

Yes. you are right,I must miss something here. it will still be char type,

I should post my code before checking the result. Anyway, Thank you very much.

Ksharp

Tal
Pyrite | Level 9 Tal
Pyrite | Level 9

hey guys,

what s behind this: input (a x1 _3) ($),the declaration inside the brackets i mean?

art297
Opal | Level 21

That is just a shorthand way of saying "input all 3 variables (the one enclosed in the brackets) as $ (with the $ enclosed in the next set of brackets)

art297
Opal | Level 21

I'd just let proc sql do ALL of the heavy work.  That way you can end up with a file with no name changes.  e.g.:

data have;

  input (a x1 _3) ($);

  cards;

13413 1434 1344

;

proc sql noprint;

  select catt(name,"= c_",name),

         catt(name,"= input(c_",name,",best.);")

    into :rename separated by ' ',

         :convert separated by ' '

      from dictionary.columns

        where libname='WORK' AND

              memname='HAVE'

  ;

quit;

data want (drop=c_:);

  set have (rename=(&rename.));

  &convert.

run;

Originally posted wrong version of code (updated 15nov12 9:17am)

RichardinOz
Quartz | Level 8

Or just export the whole thing as a CSV file and then import it back again - anything that looks like a number will be imported as a number.  Annoying if you do not want this to happen, but in your case just what you want.

Comma is better than Best

Re the input() function - I recommend using the comma informat instead of best.  That will take care of any non numeric symbols in your data.

Richard in Oz

NYSPhil
Calcite | Level 5

I am unable to access the webpage that you have entered - I got a message that I had to login.  Then, even after I logged in w/ email & password, I was still blocked.

Howles
Quartz | Level 8

Strange. Are you at work? Does your IT department block certain sites?

peterz
Calcite | Level 5

How about:

data chars;

a='1';

b='2';

c='3';

output;

a='11';

b='21';

c='31';

output;

run;

data _null_;

length varnames $1000;

set chars (obs=1);

array _chars_

  • _character_;
  •      do i = 1 to dim(_chars_);  

              if vname(_chars_) ne 'varnames' then varnames=left(trim(varnames))!!' '!!vname(_chars_);

         end;

         call symput('varnames',varnames);

    run;

    %put &varnames;

    %macro char_num();

    data nums(drop=c_:);

    set chars       (rename=(     

    %do i=1 %to %sysfunc(countw(&varnames));        

         %scan(&varnames,&i)=c_%scan(&varnames,&i)     

    %end;     

    ));  

    %do i=1 %to %sysfunc(countw(&varnames));     

         %scan(&varnames,&i)=input(c_%scan(&varnames,&i),best.);  

    %end;

    run;

    %mend char_num;

    %char_num;

    sas-innovate-2024.png

    Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

    Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

     

    Register now!

    How to Concatenate Values

    Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

    Find more tutorials on the SAS Users YouTube channel.

    Click image to register for webinarClick image to register for webinar

    Classroom Training Available!

    Select SAS Training centers are offering in-person courses. View upcoming courses for:

    View all other training opportunities.

    Discussion stats
    • 15 replies
    • 8169 views
    • 2 likes
    • 10 in conversation