Extract the value from character string

[New_SAS_user76]

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.

[Kurt_Bremser]

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

[RW9]

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

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.

[Jagadishkatam]

with perl regular expression

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