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
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;
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;
Alternatively with perl regular expression
data want;
set have;
name_nbr=prxchange('s/(.*\~)(.*)/$2/',-1,name);
name=prxchange('s/(.*)(\~.*)/$1/',-1,name);
run;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.