How to convert character variable to numeric variable

[nbonda]

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;

[art297]

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

[Kurt_Bremser]

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

[art297]

@Kurt_Bremser: 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

[Kurt_Bremser]

---

@art297 wrote:

@Kurt_Bremser: 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.

[art297]

@Kurt_Bremser: 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

[Kurt_Bremser]

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.

[art297 · Accepted solution]

@Kurt_Bremser: 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

[nbonda]

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

[Kurt_Bremser]

---

@nbonda wrote:

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

---

IIRC, it was added with version 9.

[Kurt_Bremser]

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.