DATA Step, Macro, Functions and more

How to read a column having different currency formatted data.

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

How to read a column having different currency formatted data.

Hi,

I've a csv data file that have a salary column. Salary is stored from globally so having dollar, euro, pond... etc symbol. Need help to read such type data into sas.

Example :
Salary
$30,000
€25,000
€40,000
¥80,000
...

Please help me to read this data in sas.

Thanks
Nikhil Jain

Accepted Solutions
Solution
‎07-15-2018 06:24 AM
Respected Advisor
Posts: 4,797

Re: How to read a column having different currency formatted data.

[ Edited ]
Posted in reply to Nikhiljain22740

@Nikhiljain22740

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;

 

View solution in original post


All Replies
Solution
‎07-15-2018 06:24 AM
Respected Advisor
Posts: 4,797

Re: How to read a column having different currency formatted data.

[ Edited ]
Posted in reply to Nikhiljain22740

@Nikhiljain22740

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;

 

Super User
Posts: 10,850

Re: How to read a column having different currency formatted data.

Patrick,

You gonna know €25,000 is expressed as  25.000 not 25000

Respected Advisor
Posts: 4,797

Re: How to read a column having different currency formatted data.

[ Edited ]

@Ksharp

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 

Occasional Contributor
Posts: 5

Re: How to read a column having different currency formatted data.

Posted in reply to Nikhiljain22740
Thanks Patrick, It'll work but is there any generic solution like any format that we can apply on it.
Respected Advisor
Posts: 4,797

Re: How to read a column having different currency formatted data.

Posted in reply to Nikhiljain22740

@Nikhiljain22740

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.

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 99 views
  • 0 likes
  • 3 in conversation