topic Re: Macro to convert character to numeric variable in SAS Programming

Macro to convert character to numeric variable

saslove — Wed, 09 May 2012 01:14:06 GMT

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.

Re: Macro to convert character to numeric variable

Linlin — Wed, 09 May 2012 01:38:29 GMT

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

Re: Macro to convert character to numeric variable

Ksharp — Wed, 09 May 2012 07:38:53 GMT

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

Re: Macro to convert character to numeric variable

JasonDiVirgilio — Wed, 09 May 2012 13:53:28 GMT

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;

Re: Macro to convert character to numeric variable

saslove — Wed, 09 May 2012 13:59:18 GMT

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.

Re: Macro to convert character to numeric variable

Astounding — Wed, 09 May 2012 13:59:39 GMT

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.

Re: Macro to convert character to numeric variable

saslove — Wed, 09 May 2012 14:02:45 GMT

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?

Re: Macro to convert character to numeric variable

Linlin — Wed, 09 May 2012 14:23:14 GMT

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

Re: Macro to convert character to numeric variable

Astounding — Wed, 09 May 2012 14:47:32 GMT

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.

Re: Macro to convert character to numeric variable

shivas — Wed, 09 May 2012 15:00:31 GMT

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

Re: Macro to convert character to numeric variable

JasonDiVirgilio — Wed, 09 May 2012 15:01:33 GMT

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

Re: Macro to convert character to numeric variable

Astounding — Wed, 09 May 2012 15:25:43 GMT

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

Re: Macro to convert character to numeric variable

Ksharp — Thu, 10 May 2012 07:39:58 GMT

Opps. Missing the important thing.

Re: Macro to convert character to numeric variable

data_null__ — Thu, 10 May 2012 10:35:14 GMT

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

Re: Macro to convert character to numeric variable

Astounding — Thu, 10 May 2012 13:22:40 GMT

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

Re: Macro to convert character to numeric variable

data_null__ — Thu, 10 May 2012 14:22:25 GMT

I'm very good at nothing.:smileyshocked: