You can split the source string into the currency symbol and the amount as done in the code below.
You will need lookup tables for anything else like conversion to 3 letter currency codes or conversion of currency amounts to a baseline currency.
data have;
infile datalines truncover;
input amount_string $20.;
currency_amt=input(compress(amount_string,'.','kd'),best32.);
currency_symbol=substrn(amount_string,1,anydigit(amount_string)-1);
datalines;
$30,000
€25,000
€40,000
¥80,000
;
run;
You can split the source string into the currency symbol and the amount as done in the code below.
You will need lookup tables for anything else like conversion to 3 letter currency codes or conversion of currency amounts to a baseline currency.
data have;
infile datalines truncover;
input amount_string $20.;
currency_amt=input(compress(amount_string,'.','kd'),best32.);
currency_symbol=substrn(amount_string,1,anydigit(amount_string)-1);
datalines;
$30,000
€25,000
€40,000
¥80,000
;
run;
Patrick,
You gonna know €25,000 is expressed as 25.000 not 25000
I've interpreted the comma as thousand separator so the value should become 25000
In case the comma is the decimal separator then the function reading the amount would need to look like:
currency_amt=input(compress(amount_string,',','kd'),commax32.);
....and I would need to see a string with actual decimals to come up with the final function suitable for the string pattern at hand.
In my country of origin the value would be written: 25'000,00
As far as I know there isn't any SAS provided format to convert currency symbols into currency codes. It shouldn't be that hard though to create one.
As for currency conversion: There can't be a pre-fabricated format. You'll need a currency conversion table where you lookup conversion rates with some date_key, currency_key type approach.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.