BookmarkSubscribeRSS Feed
ruchir_m
Fluorite | Level 6

 

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

6 REPLIES 6
SASKiwi
PROC Star

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

ruchir_m
Fluorite | Level 6

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

SASKiwi
PROC Star

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.

ruchir_m
Fluorite | Level 6
 
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. 
 

Screenshot_20170821-021628-01.png
 
ruchir_m
Fluorite | Level 6
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;
SASKiwi
PROC Star

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/...

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

Tips for filtering data sources in SAS Visual Analytics

See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 6 replies
  • 1654 views
  • 1 like
  • 2 in conversation