topic Re: extract string with conditions in SAS Programming

extract string with conditions

harrylui — Wed, 17 Jun 2020 02:23:58 GMT

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

Re: extract string with conditions

r_behata — Wed, 17 Jun 2020 02:33:01 GMT

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;

Re: extract string with conditions

Patrick — Wed, 17 Jun 2020 04:22:18 GMT

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;

Re: extract string with conditions

Jagadishkatam — Wed, 17 Jun 2020 10:17:18 GMT

Alternatively with perl regular expression

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