Solved
Frequent Contributor
Posts: 122

Hi all,

I have a question here. I want to convert a variable into dollar value using exchange rate. Specifically, I have a dataset with variables fund, code, asset and month, and another dataset with variables exchange rates.

dataset A

fund          code          asset          month

1               usd           xx               199001

2               aud           xxx             199002

3               gbp           xx               199202

...

dataset B

date           jpy          aud          gbp

199001        xx          xxx          xxxxx

199002        xxx          xxxx          xxx

....

I merge two datasets together via the date variable (a.month=b.date). And then I try to convert asset number in A to US dollar amount by multiplying exchange rate in B by asset number. How can I do that?

Thanks.

Accepted Solutions
Solution
‎07-14-2013 02:00 AM
Super Contributor
Posts: 312

Hi Sean,

Is this what you are looking to achieve?

DATA A;

INFILE DATALINES DELIMITER="," TRUNCOVER;

INPUT FUND \$ CODE \$ ASSET MONTH \$;

DATALINES;

1,USD,5000,199001

2,AUD,2000,199002

3,GBP,7000,199202

;

RUN;

DATA B;

INFILE DATALINES DELIMITER="," TRUNCOVER;

INPUT DATE \$ JPY AUD GBP;

DATALINES;

199001,.5,.75,.90

199002,.5,.75,.90

199202,.5,.75,.90

;

RUN;

PROC TRANSPOSE DATA=B OUT=BTRANS (RENAME = COL1=EXRATE) NAME=CODE;

BY DATE;

VAR JPY AUD GBP;

RUN;

PROC SORT DATA=A;

BY MONTH CODE;

RUN;

PROC SORT DATA=BTRANS (RENAME = DATE = MONTH);

BY MONTH CODE;

RUN;

DATA WANT;

MERGE A(IN=A)

BTRANS(IN=B);

BY MONTH CODE;

IF A AND B;

RESULT = SUM(ASSET*EXRATE);

RUN;

All Replies
Solution
‎07-14-2013 02:00 AM
Super Contributor
Posts: 312

Hi Sean,

Is this what you are looking to achieve?

DATA A;

INFILE DATALINES DELIMITER="," TRUNCOVER;

INPUT FUND \$ CODE \$ ASSET MONTH \$;

DATALINES;

1,USD,5000,199001

2,AUD,2000,199002

3,GBP,7000,199202

;

RUN;

DATA B;

INFILE DATALINES DELIMITER="," TRUNCOVER;

INPUT DATE \$ JPY AUD GBP;

DATALINES;

199001,.5,.75,.90

199002,.5,.75,.90

199202,.5,.75,.90

;

RUN;

PROC TRANSPOSE DATA=B OUT=BTRANS (RENAME = COL1=EXRATE) NAME=CODE;

BY DATE;

VAR JPY AUD GBP;

RUN;

PROC SORT DATA=A;

BY MONTH CODE;

RUN;

PROC SORT DATA=BTRANS (RENAME = DATE = MONTH);

BY MONTH CODE;

RUN;

DATA WANT;

MERGE A(IN=A)

BTRANS(IN=B);

BY MONTH CODE;

IF A AND B;

RESULT = SUM(ASSET*EXRATE);

RUN;

Frequent Contributor
Posts: 122

works great. Thanks!

Posts: 5,523

Stealing and adapting some code from Scott, here is a SQL alternative:

DATA A;
INFILE DATALINES DELIMITER="," TRUNCOVER;
INPUT FUND CODE \$ ASSET MONTH :YYMMN6.;
format month yymmd7.;
DATALINES;
1,USD,5000,199001
2,AUD,2000,199002
3,GBP,7000,199202
;

DATA B;
INFILE DATALINES DELIMITER="," TRUNCOVER;
INPUT DATE :YYMMN6. JPY AUD GBP;
format date yymmd7.;
DATALINES;
199001,.5,.75,.90
199002,.5,.75,.90
199202,.5,.75,.90
;

proc sql;
create table USD_A as
select
FUND,
case CODE
when "USD" then ASSET
when "JPY" then ASSET*JPY
when "AUD" then ASSET*AUD
when "GBP" then ASSET*GBP
else .X
end as ASSET_USD,
MONTH
from A inner join B on A.MONTH=B.DATE;
select * from USD_A;
quit;

I assumed that by dollar, you meant USD. .X is a special missing value that prints as the letter X.

PG

PG
Frequent Contributor
Posts: 122