Macro to convert character to numeric variable

[saslove]

I have this macro to convert character to numeric variable for more than 100 variables. But it doesn't seem to work and gives me an error:   180-322: Statement is not valid or it is used out of proper order.

My code is as below:

%macro numeric (var);

data recode2005_7;

set recode2005_6;

&var _c=input(&var ,8.);

run;

%mend numeric;

%numeric (age sex grade am_indi... var 100);

The SAS log highlights age and pops the above error. Not sure what it is. Tried putting var= on macro statement and other alternatives, but doesn't work.

[Linlin]

Hi,

you need to change

&var_c=input(&var ,8.);   to

var_c=input(&var ,8.); 

%macro numeric (var);

data recode2005_7;

set recode2005_6;

var_c=input(&var ,8.);

run;

%mend numeric;

Message was edited by: Linlin

[Ksharp]

Your code is bad. About your macro, there is only one macro variable parameter.

From the content of code, it only can process one variable each time. So invoke the macro each time for each variable.

%macro numeric (var);

data recode2005_7;

set recode2005_6;

&var_c=input(&var ,8.);

run;

%mend numeric;

%numeric (age )

%numeric (var1)

%numeric (var2)

....

Ksharp

[JasonDiVirgilio · Accepted solution]

And I believe if you intended to name the character variable based on the paramter that you pass you'll need a period before the "_c" in the name (slight modification to Ksharp's code)

%macro numeric (var);

    data recode2005_7;

      set recode2005_6;

      &var._c=input(&var ,8.);

  run;

%mend;

[saslove]

Thanks a lot for your replies. It still doesn't seem to work. I tried all of these options. It gives me the same error

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

[Astounding]

A few comments ...

This code works for a single variable.  If you have a list of variables you want to convert using the same logic, you still have a lot of work to do.

Your code would be a lot more useful if you would leave the DATA and SET statements out of the macro definition.

Good luck.

[saslove]

This seems to work , but is there an easy way I could use use all variables in one macro statement instead of calling the macro 167 times?

[Linlin]

why I keep posting the wrong code? hope this one is the one I intented to post.:smileysilly:

Hi,

you can modify the code below to convert your character variables to numeric variables.

data have;
length date1 date2 $10;
  input age weight (id sex a b c d e)($)  date1 date2;
  cards;
100 150 1 2 11 22 33 44 55  01/12/2012 2/12/2012
;
proc sql noprint;

select catx(' ','input(',name,',best32.) as',name) into : list1 separated by ','
  from dictionary.columns
   where libname='WORK' and memname='HAVE' and type ='char' and name not in ('id','sex','date1','date2');

/* &list1 includes all the character variables you want to convert to numeric. In the example, id,sex,date1,date2 were excluded. */

select name into : list2 separated by ','  from dictionary.columns 
   where libname='WORK' and memname='HAVE' and type ='num' ;

/* &list2 includes all the numeric variables */

select catx(' ','input(',name,',mmddyy10.)  format=mmddyy10. as',name)  into : list3 separated by ','  from dictionary.columns
   where libname='WORK' and memname='HAVE' and name in ('date1','date2');

/* &list3 includes all the date variables */

create table want as
  select id,sex,&list1,&list2,&list3 from have;

quit;
proc contents data=want;run;proc print;run;

Message was edited by: Linlin

[Astounding]

Linlin's code raises an interesting point.  Perhaps it would be easier (and less prone to error) to specify a list of character variables that should NOT be converted, rather than a list of those that should be converted.  At any rate, here's an approach that continues along the lines of what you have seen so far:

%macro convert (varlist);

   %local i nextvar;

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

          %let nextvar = %scan(&varlist, &i, %str( ));

          &nextvar._c = input(&nextvar, 8.);

   %end;

%mend convert;

data want;

   set have;

   %convert (age sex grade)

run;

The idea is to loop through the list of variable names, and for each one generate a statement to convert it to numeric.

By the way, I followed your lead in naming the new variables with "_c".  It might be less confusing to name them using "_n" since these are the numeric versions not the character versions.

Good luck.

[shivas]

Hi,

Try this..

data have;

input (age sex grade) ($);

cards;

2 4 8

6 6 10

5 7  8

;

run;

data want;

set have;

array xx(3) age sex grade;

array yy(3) newage newsex newgrade;

do i = 1 to dim(xx);

yy(i)=input(xx(i),8.);

end;

drop age--grade i;

run;

Thanks,

Shiva

[JasonDiVirgilio]

ahhhh...countw() is available in SAS9.2...I want that function!

[Astounding]

Yes, it saves the trouble of writing a macro (or writing a complex %DO  loop) to perform the counting.  The only problem I have seen is that the macro version

%sysfunc(countw(&varlist))

complains loudly when the incoming string is null.  For some applications, I end up coding

%if %length(&varlist) %then %do i=1 %to %sysfunc(countw(&varlist));

[data_null__]

Quote it.

Astounding wrote:

Yes, it saves the trouble of writing a macro (or writing a complex %DO  loop) to perform the counting.  The only problem I have seen is that the macro version

%sysfunc(countw(&varlist))

complains loudly when the incoming string is null.  For some applications, I end up coding

%if %length(&varlist) %then %do i=1 %to %sysfunc(countw(&varlist));

4    %let varlist=;

5    %put NOTE: %sysfunc(countw(&varlist));

ERROR: The function COUNTW referenced by the %SYSFUNC or %QSYSFUNC macro function has too few arguments.

NOTE: .

6    %put NOTE: %sysfunc(countw(%superQ(varlist)));

NOTE: 0

Seems like all these "solutions" have a major flaw. 

• INFORMAT
• Name is changed
• Label is lost

[Astounding]

Excellent. I should have known ... if you have a problem with a null value, ask data_null_; 

[data_null__]

I'm very good at nothing.:smileyshocked: