extract string with conditions

[harrylui]

good day all,

Here is some examples in my data set. i want to extract the number after the symbol ~  and store them into a new column

Name

EDEKA AKTIVMARKT HARMS QUICKBORN DE~140485089
EDEKA AKTIVMARKT JENA DEU~158665920
EDEKA AM BARBAROSSAPL KOELN DE~148543912

ideal demonstration:

Name                                                                              name_nbr

EDEKA AKTIVMARKT HARMS QUICKBORN DE        140485089

Thanks in advance,

Harry

[r_behata]

Data have;
input Name $100.;
infile cards truncover;
cards;
EDEKA AKTIVMARKT HARMS QUICKBORN DE~140485089
EDEKA AKTIVMARKT JENA DEU~158665920
EDEKA AM BARBAROSSAPL KOELN DE~148543912
;
run;
data want;
	set have(rename=name=name_);
	Name=scan(name_,1,'~');
	name_nbr=scan(name_,-1,'~');

	drop name_;
run;

[Patrick]

Or here as a variation to @r_behata if you want to store the digits in a numerical variable.

Data have;
  input Name $100.;
  infile cards truncover;
  cards;
EDEKA AKTIVMARKT HARMS QUICKBORN DE~140485089
EDEKA AKTIVMARKT JENA DEU~158665920
EDEKA AM BARBAROSSAPL KOELN DE~148543912
;
run;

data want;
  set have;
  Number=input(scan(name,-1,'~'),?? best32.);
  Name=scan(name,1,'~');
run;

proc print data=want;
run;

Patrick_0-1592367721313.png

[Jagadishkatam]

Alternatively with perl regular expression

data want;
set have;
name_nbr=prxchange('s/(.*\~)(.*)/$2/',-1,name);
name=prxchange('s/(.*)(\~.*)/$1/',-1,name);
run;