BookmarkSubscribeRSS Feed
harrylui
Obsidian | Level 7

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

3 REPLIES 3
r_behata
Barite | Level 11
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
Opal | Level 21

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
Amethyst | Level 16

Alternatively with perl regular expression

 

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

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 1041 views
  • 0 likes
  • 4 in conversation