DATA Step, Macro, Functions and more

How to convert character variable to numeric variable

Accepted Solution Solved
Reply
Contributor
Posts: 40
Accepted Solution

How to convert character variable to numeric variable

data test1:
ID="0000102+";
SEQ_NO="000932+";
BALANCE="003234.32+";
DTM='09mAY2017:02:00:00'DT;
FORMAT DTM DATETIME20.;
RUN;

I have to modify above TEST1 dataset in two scenarios.
1. I need to remove end + signs in Character variables;
2. I need to modify those character variables into numeric variables.

 

I have used below program to accomplish.

It's not working. Please let me know the how to correct the code.

 

data test;

attrib

id     length=8 format=19. informat=19.

seq_no length=8 format=19. informat=19.

balance length=8 format=22.2 informat=22.2 ;

set test1 (rename=(id=id_ seq_no=seq_no_ balance=balance_));

array col_name_old {3} id_ seq_no_ balance_;

array col_name_new {3} id seq_no  balance;

do i=1 to 3;

   if find(col_name_old{i}, "+") = length(col_name_old{i}) then do;

      col_name_old{i} = compress(col_name_old{i},"+");

     col_names_new{{i} = col_name_old{i};

   end;

end;

run;

 

 


Accepted Solutions
Solution
‎08-07-2017 10:46 AM
PROC Star
Posts: 7,471

Re: How to convert character variable to numeric variable

Posted in reply to KurtBremser

@KurtBremser: My error. There was a typo in my code. I had used (as posted):

  balance=input(balance,trailsgn10.);

rather than:

  balance=input(_balance,trailsgn10.);

Interestingly, that doesn't produce an uninitialized note.

 

Art, CEO, AnalystFinder.com

View solution in original post


All Replies
PROC Star
Posts: 7,471

Re: How to convert character variable to numeric variable

You can use arrays but, for only 3 variables, they aren't needed. e.g.:

 

data test1;
  ID="0000102+";
  SEQ_NO="000932+";
  BALANCE="003234.32+";
  DTM='09mAY2017:02:00:00'DT;
  FORMAT DTM DATETIME20.;
RUN;

data test (drop=_:);
  set test1 (rename=(id=_id seq_no=_seq_no balance=_balance));
  id=input(compress(_id,"+"),8.);
  balance=input(compress(_balance,"+"),8.);
  seq_no=input(compress(_seq_no),8.);
run;

Art, CEO, AnalystFinder.com

 

Super User
Posts: 7,771

Re: How to convert character variable to numeric variable

For strings with a trailing sign, use the trailsgn. format.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
PROC Star
Posts: 7,471

Re: How to convert character variable to numeric variable

Posted in reply to KurtBremser

@KurtBremser: Interestingly, for ID the trailsgn informat fails unless one specifies a width of 7 or more, and doesn't work at all if the field contains a decimal point (like, in this case, balance).

 

Art, CEO, AnalystFinder.com

 

 

Super User
Posts: 7,771

Re: How to convert character variable to numeric variable

[ Edited ]

art297 wrote:

@KurtBremser: Interestingly, for ID the trailsgn informat fails unless one specifies a width of 7 or more, and doesn't work at all if the field contains a decimal point (like, in this case, balance).

 

Art, CEO, AnalystFinder.com

 

 


I have to differ.

Run this:

data test1;
input id trailsgn10.;
cards;
0000102+
000932+
003234.32+
;
run;

data test2;
input _id $10.;
id = input(_id,trailsgn10.);
cards;
0000102+
000932+
003234.32+
;
run;

Both steps produce correct numerical values.

 

Edit: fixed a typo.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
PROC Star
Posts: 7,471

Re: How to convert character variable to numeric variable

Posted in reply to KurtBremser

@KurtBremser: The problem is with the Balance field. I ran the following on SAS UE:

data test2 (drop=_:);
  set test1 (rename=(id=_id seq_no=_seq_no balance=_balance));
  id=input(_id,trailsgn7.);
  balance=input(balance,trailsgn10.);
  seq_no=input(_seq_no,trailsgn8.);
run;

Of course, since the defalut is only 6, the width has to be specified for each variable. However, BALANCE results in a missing value.

 

Art, CEO, AnalystFinder.com

 

Super User
Posts: 7,771

Re: How to convert character variable to numeric variable

Not possible for the value of balance as specified in the OP and used in my last post.

Run this:

data test;
_balance = "003234.32+";
balance = input(_balance,trailsgn10.);
run;

proc print data=test noobs;
run;

Result:

 _balance     balance

003234.32+    3234.32

No missing value there.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Solution
‎08-07-2017 10:46 AM
PROC Star
Posts: 7,471

Re: How to convert character variable to numeric variable

Posted in reply to KurtBremser

@KurtBremser: My error. There was a typo in my code. I had used (as posted):

  balance=input(balance,trailsgn10.);

rather than:

  balance=input(_balance,trailsgn10.);

Interestingly, that doesn't produce an uninitialized note.

 

Art, CEO, AnalystFinder.com

Contributor
Posts: 40

Re: How to convert character variable to numeric variable

Thank you. It works. I never knew there is a informat like trailsgn.

Super User
Posts: 7,771

Re: How to convert character variable to numeric variable


nbonda wrote:

Thank you. It works. I never knew there is a informat like trailsgn.


IIRC, it was added with version 9.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 7,771

Re: How to convert character variable to numeric variable

The "unitialized" NOTE is not created dynamically, but syntactically while the step is compiled. Since the compiler finds an assignment to balance, it does not issue a NOTE; obviously it does not check if it's a recursive assignment.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
☑ This topic is solved.

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

Discussion stats
  • 10 replies
  • 155 views
  • 0 likes
  • 3 in conversation