topic Re: SAS VA : Local to USD currency conversion in SAS Visual Analytics

SAS VA : Local to USD currency conversion

ruchir_m — Sat, 19 Aug 2017 12:40:45 GMT

Hi VA Developer,

I have data from multiple countries (~10) from in a singe table . Now I have challenge with Local to USD currency conversion. I need to have data in both currency as report viewer can be local or global (USD) . I need to have a drop down with Local and USD currency.

Can anyone please help me with optimized solution .

Thanks
Ruchir

Re: SAS VA : Local to USD currency conversion

SASKiwi — Sat, 19 Aug 2017 23:00:40 GMT

Have you already sourced the currency exchange rates and they are in your data?

Re: SAS VA : Local to USD currency conversion

ruchir_m — Sun, 20 Aug 2017 11:23:56 GMT

No, I have not. I am planning to create to table with exchange rates and later have a join with master Data.

Re: SAS VA : Local to USD currency conversion

SASKiwi — Sun, 20 Aug 2017 20:21:46 GMT

Your exchange rates will need to be in the same table as the currency data for you to do the currency conversion calculations and to display your analysis in a single report object. It would help if you explained what type of report(s) you want.

You could have a separate dropdown list for the chosen currency, then have an interraction that filters your other report object by the currency. This would require you to have each currency on a separate row in your source data.

Re: SAS VA : Local to USD currency conversion

ruchir_m — Sun, 20 Aug 2017 20:59:55 GMT

I want to have SAS Va report . I have sas va report viewer as local as well as global . So if local viewer want to see the report they can see it as per their local currency but global report viewer will see report in global currency (consider them as higher managment sits in US)

I have created a single table with help of join with fx rates table which have amount columns (14) as well country and fx rate of it.

So I want to know how I can apply this in VA with drop down or something.

kindly consider read data is huge.

Pfa as sample table structure.

Re: SAS VA : Local to USD currency conversion

ruchir_m — Mon, 21 Aug 2017 02:09:43 GMT

Created Sample data with following code :

data Master_ds;
infile datalines delimiter=',';
length Country $ 13;
Input Country $ Acc_blnc_amt Loan_blnc_amt Credit_blnc_amt Mrtg_blnc_amt;
datalines;
Singapore,8326,2108,2345,4815
HongKong,7100,3428,2902,6203
United States ,3841,5429,2221,6053
Malaysia,8207,5120,3309,2772
Singapore,7033,5897,4622,3293
HongKong,4292,5269,5003,7165
United States ,3110,6166,7239,2450
Malaysia,6494,2328,2391,8260
Singapore,1612,5631,6002,8903
HongKong,7659,8425,6857,5967
United States ,6420,1260,6175,2518
Malaysia,1135,7452,3288,2068
;

Data Fx_Cnvsn;
infile datalines delimiter=',';
length Country $ 13;
Input Country $ Fx_rate_USD;
datalines;
Singapore,1.3
HongKong,7.8
United States,1
Malaysia,4.2
;

proc SQL NOPRINT;
Select DISTINCT name
INTO:amt_colms
SEPARATED BY "}"
from Sashelp.Vcolumn
Where
libname='WORK'
AND
memname='MASTER_DS'
AND
upcase(name) like '%AMT';
Quit;

%Put &amt_colms;

PROC SQL;
Create Table Temp AS
SELECT *
FROM Master_ds,Fx_Cnvsn
WHERE Master_ds.Country = Fx_Cnvsn.Country;
QUIT;

%Macro Currency_Conv;
Data Final_DS;
Set Temp;
%Let iter = 1;
%Do %While(%scan(&amt_colms., &iter., %str(})) ne %str());
%let curr_loop_amt = %scan(&amt_colms., &iter., %str(}));
&curr_loop_amt._USD = &curr_loop_amt. * Fx_rate_USD;
%Let iter = %eval(&iter. + 1);
%End;
Run;

%Mend;

%Currency_Conv;

Re: SAS VA : Local to USD currency conversion

SASKiwi — Mon, 21 Aug 2017 07:32:32 GMT

I think row-level security might be useful here. With RLS, a user who works in a particular country would only be able to see his/her own country's currency data, while head office would be enabled to see all currencies. I'm not an expert on this topic so I'm hoping someone with experience in RLS can help out.

This link should point you in the right direction:

https://communities.sas.com/t5/SAS-Visual-Analytics/SAS-Visual-Analytics-7-3-Row-Level-Security/m-p/287461#M4900