topic Separate Number and characters in New SAS User

Separate Number and characters

SASPhile — Mon, 09 Mar 2020 14:45:57 GMT

A variable has mix of numbers and characters. I would want to separate numbers and characters into two variables:

Raw Variable

2-5 TIMES IN WEEK

4-NORMAL

2018-09-27 Last Visit

4 - severe problems walking

Raw Variable	Character_Var	Numeric_Var
2-5 TIMES IN WEEK	2-5 TIMES IN WEEK
4-NORMAL NORMAL	4
2018-09-27 Last Visit	2018-09-27 Last Visit
4 - severe problems walking	severe problems walking	4

I tried this anydigit(substr(Raw_Variable ,1,1))>0 and substr(Raw_Variable ,2,1)='-' to separate out ,but did not seem to handle properly all the cases. Any help is appreciated

Re: Separate Number and characters

Patrick — Mon, 09 Mar 2020 15:37:33 GMT

I'd really appreciate if you could post your sample data in the form of a working SAS datastep as done below. I consider this a form of courtesy to the people wanting to help you (so they don't have to do it for you).

data have;
  input string $30.;
  datalines;
2-5 TIMES IN WEEK
4-NORMAL
2018-09-27 Last Visit
4 - severe problems walking
;

As for your question: There is something off with your 3rd line in the desired result. String "NORMAL" is in a separate column without heading. Can you please fix or explain further?

Raw Variable	Character_Var	Numeric_Var
2-5 TIMES IN WEEK	2-5 TIMES IN WEEK
4-NORMAL NORMAL	4
2018-09-27 Last Visit	2018-09-27 Last Visit
4 - severe problems walking	severe problems walking	4

Re: Separate Number and characters

SASPhile — Mon, 09 Mar 2020 16:37:11 GMT

NORMAL to goes to Character_Var column. Thanks for pointing it out!

Re: Separate Number and characters

Tom — Mon, 09 Mar 2020 17:15:34 GMT

VERIRY() might be a useful function for you.

data have;
  input string $30.;
  datalines;
2-5 TIMES IN WEEK
4-NORMAL
2018-09-27 Last Visit
4 - severe problems walking
;

data want ;
  set have ;
  loc=verify(string,'0123456789-. ');
  length numpart rest $30;
  numpart=substrn(string,1,loc-1);
  rest=substrn(string,loc);
run;
proc print;
run;

Obs    string                         loc    numpart       rest

 1     2-5 TIMES IN WEEK                5    2-5           TIMES IN WEEK
 2     4-NORMAL                         3    4-            NORMAL
 3     2018-09-27 Last Visit           12    2018-09-27    Last Visit
 4     4 - severe problems walking      5    4 -           severe problems walking

Re: Separate Number and characters

SASPhile — Mon, 09 Mar 2020 17:42:40 GMT

Hi Tom,

Its only the second and foruth record that needs parsing.

Re: Separate Number and characters

Tom — Mon, 09 Mar 2020 18:03:29 GMT

@SASPhile wrote:

Hi Tom,

Its only the second and foruth record that needs parsing.

To not find those two then do not include the hyphen as one of the valid characters in the VERIFY() call.

But can you have negative values like: -123? Are you only looking for integers? What about values like 1.5 ? How about scientific notation, 1.2E23 ? What about percentages like: 10%? What commas separating thousands groups like: 1,234?

Do you want try converting the value to an actual number? Try using the INPUT() function. What informat to want to use?