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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.