How to convert many character variables to numeric using a macro ?

Accepted Solution Solved
Reply
Contributor
Posts: 34
Accepted Solution

How to convert many character variables to numeric using a macro ?


Hi, this macro works for only one variable :

%macro numeric (file1,file2,charvar);

data &file2(rename=(&charvar._n=&charvar));

set &file1;

&charvar._n=input(&charvar ,8.);

drop  &charvar;

run;

%mend;

Question : Depending on the result of the proc import, I may have to convert 20 variable or just 5. How to set-up a loop in this macro so it can be executed this way : %numeric (file1,file2,charvar1, charvar2....charvar_n) ?

Thanks.


Accepted Solutions
Solution
‎08-16-2014 10:19 PM
Super User
Super User
Posts: 6,365

Re: How to convert many character variables to numeric using a macro ?

I agree that you are better off solving this type issue at the source than trying to deal with it later.  PROC IMPORT is a great tool for ad hoc projects, but for serious work you are better off having defined standard formats for input files and code that reads them into the proper data structures..

But if you do want to create such a macro consider a few simplifying concepts.

1) When passing a indefinite number of names into a macro use a space delimited list (like in normal SAS code like a VAR or TABLES statement) and not a comma delimited list. This will allow you to easily pass all the names as one parameter to your macro.

2) You can automate some of this by using metadata (PROC CONTENTS output or DICTIONARY.COLUMNS or SASHELP.VCOLUMN) to check which variables have the wrong type.

3) Do you want a macro that handles the whole problem or generates part of the code that you can incorporate into a larger problem?

So in the simple case where you know which variables need to be converted you could do a simple macro that just does them one at a time and just generates part of data step.

%macro to_numeric(varlist);

%local i var;

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

   %let var=%scan(&varlist,&i);

   _n_&i = input(&var,??32.);

   drop &var;

   rename _n_&i = &var;

%end;

%mend to_numeric;

data file1 ;

  set file1 ;

  %to_numeric(field1 field6);

run;

If you want to make it more sophisticated you have it decide which fields in which files need to be converted.

proc sql;

create table rename as

  select catx('.',libname,memname) as dataset

       , name

  from dictionary.columns

  where libname='WORK'

    and type='char'

    and indexw("%upcase(&varlist)",upcase(name))

  ;

quit;

You could even have it generate code that would preserve the variable order and labels of the original variables.

View solution in original post


All Replies
Super User
Posts: 1,117

Re: How to convert many character variables to numeric using a macro ?

please try the below code

i wrote the below code expecting that the proc import character variable names are ending with numeric something like charvar1,charvar2  and so on

%macro numeric (file1,file2,charvar,numvar);

data &file2;

set &file1;

array num(&n) &numvar1 - &numvar._&n;

array cha(&n) &charvar1 - &charvar._&n;

%do i = 1 %to &n;

&numvar._&i=input(&charvar._&i ,8.);

%end;

drop &charvar:;

run;

%mend;

Thanks,

Jag

Thanks,
Jag
Grand Advisor
Posts: 10,225

Re: How to convert many character variables to numeric using a macro ?

This may be a symptom that you don't want to use proc import. You don't mention the source file but if it is any form of text file, CSV, tab delimited or similar, and especially if the data is supposed to be in the same layout then going to a data step that reads the data will give you the control of informats and variable type.

Trusted Advisor
Posts: 1,203

Re: How to convert many character variables to numeric using a macro ?

I think you are getting these converted variables as a result of proc import. If that is the case then try to fix this while importing the data.

Solution
‎08-16-2014 10:19 PM
Super User
Super User
Posts: 6,365

Re: How to convert many character variables to numeric using a macro ?

I agree that you are better off solving this type issue at the source than trying to deal with it later.  PROC IMPORT is a great tool for ad hoc projects, but for serious work you are better off having defined standard formats for input files and code that reads them into the proper data structures..

But if you do want to create such a macro consider a few simplifying concepts.

1) When passing a indefinite number of names into a macro use a space delimited list (like in normal SAS code like a VAR or TABLES statement) and not a comma delimited list. This will allow you to easily pass all the names as one parameter to your macro.

2) You can automate some of this by using metadata (PROC CONTENTS output or DICTIONARY.COLUMNS or SASHELP.VCOLUMN) to check which variables have the wrong type.

3) Do you want a macro that handles the whole problem or generates part of the code that you can incorporate into a larger problem?

So in the simple case where you know which variables need to be converted you could do a simple macro that just does them one at a time and just generates part of data step.

%macro to_numeric(varlist);

%local i var;

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

   %let var=%scan(&varlist,&i);

   _n_&i = input(&var,??32.);

   drop &var;

   rename _n_&i = &var;

%end;

%mend to_numeric;

data file1 ;

  set file1 ;

  %to_numeric(field1 field6);

run;

If you want to make it more sophisticated you have it decide which fields in which files need to be converted.

proc sql;

create table rename as

  select catx('.',libname,memname) as dataset

       , name

  from dictionary.columns

  where libname='WORK'

    and type='char'

    and indexw("%upcase(&varlist)",upcase(name))

  ;

quit;

You could even have it generate code that would preserve the variable order and labels of the original variables.

Valued Guide
Posts: 3,206

Re: How to convert many character variables to numeric using a macro ?

Tom why putting all the effort on trying to solve symptons and not going for working on  the root cause? 
Aside that the numeric character question is confusing there 10 people understanding the digital world.
It should be better designed as: classification measurement and time/date-s the latter is even more difficult to do right.

---->-- ja karman --<-----
Contributor
Posts: 34

Re: How to convert many character variables to numeric using a macro ?

Thanks for your replies,

For those asking me to fix the root of the problem, they have to understand that I have no control on the files I recveive from other departments

and I have to find quick solutions to analyze them. That's why I try to rely on macros.

I am aware that converting an excel file to a text file (and then using infile) gives full control on the process. But the problem is then to find the delimiter : I have many long text fields that have ; and , inside them so these delimiters won't work; and the tab delimited file did not work too.

Esteemed Advisor
Esteemed Advisor
Posts: 7,245

Re: How to convert many character variables to numeric using a macro ?

Then use something other than ; for example ¬.

infile "file.txt" dlm="¬"...

However stretching a bit further, export the data to text having "quotes around the data","quotes around some other data"...  Here is an example of how to do it (I haven't tested!) Export Excel CSVs with Double Quotes

You can link that VBA up with a directory listing macro: http://excelexperts.com/VBA-Tips-List-Files-In-A-Folder

To create a process Excel file which will read all your data and export proper CSV data.

I agree with the above posters, whilst it may seem to be ok writing programs to create data, you could end up in problems, e.g how to know which variables are to be numeric, what type to assign them etc.

A final thought, if you have no control over files sent to you, then you will probably end up doing it manually each time anyway.  The simple fact is that pretty much everyone uses Excel in their own, and its flexibility is its own downfall.  With no import agreements in place, the spreadsheets you get will like as not be different each time you get them.  That "oh yes, there wasn't a value but we didn't want that missing so we put in NA instead" mentality will ruin any standard programs you try to write.

Grand Advisor
Posts: 10,225

Re: How to convert many character variables to numeric using a macro ?

The garbage data line NA in what should be a numeric field is exactly why I convert files to CSV and read them.

When read as numeric you get a missing value. In some cases I use custom informats/formats to create special missing values to differentiate between the NA and "Refused to Answer" for example. And for at least one project I turn a whole bunch of strings into dichotomous numeric as the string is basically Yes/No and analysis is much easier with 1/0. I can also use custom informats to check the validity of data that should have limited values and/or correct for changing case.

Also the option DSD is likely to be helpful.

Grand Advisor
Posts: 17,396

Re: How to convert many character variables to numeric using a macro ?

You can control the import type for a field from Excel via DBSASTYPE= option, but this is more of an "infile" method in that you'll need to hardcode it for each file rather than automatically import the file. 

SAS/ACCESS(R) 9.2 for Relational Databases: Reference, Fourth Edition

Valued Guide
Posts: 3,206

Re: How to convert many character variables to numeric using a macro ?

Agree RW9, users can be very creative in finding ways to enter data in a way you did not expect. You will know what you do not know.

When the data is manually processed as a way coming in by mail or other ad-hoc method. You will end up verifying that.

Recently seen some one without an solution.

Put in excel a hard newline alt-enter (cr-lf) in a text field an trying that in a CSV approach. In Excel it shows nice several lines in a cel.   

---->-- ja karman --<-----
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 728 views
  • 2 likes
  • 8 in conversation