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

How to convert character variable to numeric variable

nbonda — Mon, 07 Aug 2017 03:44:21 GMT

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;

run;

Re: How to convert character variable to numeric variable

art297 — Mon, 07 Aug 2017 04:07:44 GMT

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

Re: How to convert character variable to numeric variable

Kurt_Bremser — Mon, 07 Aug 2017 04:19:24 GMT

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

Re: How to convert character variable to numeric variable

art297 — Mon, 07 Aug 2017 13:30:42 GMT

@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

Re: How to convert character variable to numeric variable

Kurt_Bremser — Mon, 07 Aug 2017 13:41:06 GMT

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

Re: How to convert character variable to numeric variable

art297 — Mon, 07 Aug 2017 13:50:09 GMT

@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

Re: How to convert character variable to numeric variable

Kurt_Bremser — Mon, 07 Aug 2017 14:02:54 GMT

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.

Re: How to convert character variable to numeric variable

art297 — Mon, 07 Aug 2017 14:37:10 GMT

@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

Re: How to convert character variable to numeric variable

nbonda — Mon, 07 Aug 2017 14:48:15 GMT

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

Re: How to convert character variable to numeric variable

Kurt_Bremser — Tue, 08 Aug 2017 05:52:40 GMT

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.

Re: How to convert character variable to numeric variable

Kurt_Bremser — Tue, 08 Aug 2017 06:26:08 GMT

@nbonda wrote:

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

IIRC, it was added with version 9.