topic Extract the value from character string in New SAS User

Extract the value from character string

New_SAS_user76 — Thu, 20 Dec 2018 09:53:15 GMT

Hi all,

I want to extract the value from the charater string and save it in numeric format. Would you please kindly help me how to make it.

Txn_Desc

CAP:P-3%

CAP:P-2.75%

CAP:P-3.1%

LC:CAP:P-3.25%

HIB+1%;LC;CAP: P-4.15%

e.g. I want to extract the value 3.25 from the string : LC:CAP:P-3.25%

The extract logic should like this

Extracting the value which after the "P-" and before the "%" Sign.

Re: Extract the value from character string

Kurt_Bremser — Thu, 20 Dec 2018 10:13:38 GMT

Ideally, post example data as data steps with datalines in a code window ("little running man" or {i} button).

The main posting windows has the habit of "beautifying" text, as can be seen by the smileys in your post.

Guessing what you have, I suggest this:

data have;
infile datalines truncover;
input txn_desc $30.;
datalines4;
CAP:P-3%
CAP:P-2.75%
CAP:P-3.1%
LC:CAP:P-3.25%
HIB+1%;LC;CAP: P-4.15%
;;;;
run;

data want;
set have;
index = index(txn_desc,'P-');
_txn = substr(txn_desc,index+2);
value = input(scan(_txn,1,'%'),best.);
drop index _txn;
run;

proc print data=want noobs;
run;

Result:

txn_desc                  value

CAP:P-3%                   3.00
CAP:P-2.75%                2.75
CAP:P-3.1%                 3.10
LC:CAP:P-3.25%             3.25
HIB+1%;LC;CAP: P-4.15%     4.15

Re: Extract the value from character string

RW9 — Thu, 20 Dec 2018 10:17:21 GMT

Well, the simple answer here normally is:

want=input(compress(have,".","kd"),best.);

However you have multiple data items in one string - which is bad data modelling and hence you have to process further.

First you will need to split the string up into elements, i.e. one data cell for one data item, e.g.:

data want;
  set have;
  length wrd $200;
  do i=1 to countw(txn_desc,";");
    wrd=scan(txn_desc,i,";");
    output;
  end;
run;

Now you can then apply the compress to the new variable:

data want;
  set want;
  new_var=input(compress(wrd,".","kd"),best.);
run;

Note, not tested - post test data in the form of a datastep in future!!!

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712

Of course if it is really only that one data element, then you might be able to get away with:

data want;
  set have;
  if index(txn_desc,"P-");
  want=input(substr(txn_desc,index(txn,"P-")+2,lengthn(txn_desc)-1),best.);
run;

Again untested.

Re: Extract the value from character string

Jagadishkatam — Thu, 20 Dec 2018 10:57:58 GMT

with perl regular expression

data want;
set have;
value=input(prxchange('s/(.*p-)(.*)(%)/$2/i',-1,strip(txn_desc)),best.);
run;