Please help me with a conversion problem

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 122
Accepted Solution

Please help me with a conversion problem

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: 297

Re: Please help me with a conversion problem

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;

View solution in original post


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

Re: Please help me with a conversion problem

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

Re: Please help me with a conversion problem

works great. Thanks!

Respected Advisor
Posts: 4,651

Re: Please help me with a conversion problem

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
4,CAD,70000,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

Re: Please help me with a conversion problem

Works as a charm. Thanks!

☑ This topic is SOLVED.

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

Discussion stats
  • 4 replies
  • 258 views
  • 3 likes
  • 3 in conversation