topic Re: Macro for converting char variables to numeric with their labels in SAS Procedures

Macro for converting char variables to numeric with their labels

Sas_R — Mon, 09 Sep 2013 00:47:40 GMT

Hello,

I'm trying to convert my character variables which are more than 4000 to numeric and I'd like to also get their labels assigned to new numeric variables.

I found attached PDF document for this purpose, it's working for a few variables but since I have more than 4000 variables, it's giving error.

I was wondering if anyone has better solutions or idea to resolve this problem ?

Thank you.

Re: Macro for converting char variables to numeric with their labels

jakarman — Mon, 09 Sep 2013 09:32:46 GMT

What is your situation, exactly? 4000 variables looks to be a lot there must be some source of it. Rows / cols ?

Where is your data stores SAS or in RDBMS?

Are there limitiations that are precision related (flosting point) is binning an approach?

The approach of working with arrray-s can be popssible modified and improved for your situation.

Re: Macro for converting char variables to numeric with their labels

Vince28_Statcan — Mon, 09 Sep 2013 12:17:17 GMT

overcome the issue of macro var length by just using proc sql; with macro facility to create many variables instead

proc sql;

select name, quote(trim(label))

into :vname1-:vname9999, :vlabel1-:vlabel9999 /*It is possible you may need to break this down into 2 select statements, I don't remember if into statement supports range over multiple macro variables */

from sashelp.vcolumn

where libname = "WORK" and

memname = "HAVE" and

type = "char"

/* and any other condition to discriminate the "right" character variables */

;

quit;

%macro magic();

data want;

set have;

%do i=1 %to &sqlobs;

new_&&vname&i.. = input(&&vname&i.. , best32.);

%end;

label

%do i=1 %to &sqlobs;

new_&&vname&i.. = &&vlabel&i..

%end;

; /* closing label statements' semi column */

/* add another do loop to drop &&vname&i.. once you are satisfied with test results */

run;

%mend;

%magic();

Vincent

Re: Macro for converting char variables to numeric with their labels

Tom — Mon, 09 Sep 2013 12:34:18 GMT

You do not have to use macro logic to generate code. You can use regular SAS code.

Assume you have dataset named CONTENTS with the output from PROC CONTENTS for your variables.

filename code temp;

data _null_;

set contents ;

file code ;

temp = cats('_',_n_);

put temp '=input(' name ',' length 5. '.);'

/ 'rename ' temp '=' name ';'

/ 'drop ' name ';'

;

if label ne ' ' then put

'label ' temp '=' label :$quote. ';'

;

run;

data want ;

set have ;

%inc temp;

run;

Message was edited: Fixed DROP statement.

Re: Macro for converting char variables to numeric with their labels

data_null__ — Mon, 09 Sep 2013 12:50:03 GMT

Don't you need to drop NAME not TEMP.

data a;
   a = '123'; 
   b = input(a,f3.1);
   rename b=a;
   drop a;
   run;

Re: Macro for converting char variables to numeric with their labels

Sas_R — Mon, 09 Sep 2013 13:28:45 GMT

The original source is txt file, I loaded that into SAS but now I need to convert the variables to numeric

Re: Macro for converting char variables to numeric with their labels

Tom — Mon, 09 Sep 2013 13:40:13 GMT

Yes.