DATA Step, Macro, Functions and more

-$160 instead of $-160

Accepted Solution Solved
Reply
Contributor
Posts: 58
Accepted Solution

-$160 instead of $-160

I have two column :

 

UNITAmount
$-162
$-398
$160

 

When i concatenate My amount is coming as $-160 , but i want it to come as -$160 .

however if it positive no then it should come like $160

 


Accepted Solutions
Solution
‎08-15-2017 03:35 PM
Esteemed Advisor
Posts: 5,396

Re: -$160 instead of $-160

Since the problem involves changing a string into another string, you could use regular expression substitution:

 

data test;
input amount $;
dollarAmount = prxchange("s/([.0-9])/\$\1/",1,amount);
datalines;
-162
-398
160
;

proc print noobs; run;
PG

View solution in original post


All Replies
PROC Star
Posts: 499

Re: -$160 instead of $-160

somthing like this should work


data have;
input UNIT $ Amount;
datalines;
$ -162 
$ -398 
$ 160
;



proc sql;
select case when amount lt 0
 then '-'||strip(unit)||strip(put(abs(amount),5.))
 else strip(unit)||strip(put(amount,5.))
 end as newcol
 from have;
Solution
‎08-15-2017 03:35 PM
Esteemed Advisor
Posts: 5,396

Re: -$160 instead of $-160

Since the problem involves changing a string into another string, you could use regular expression substitution:

 

data test;
input amount $;
dollarAmount = prxchange("s/([.0-9])/\$\1/",1,amount);
datalines;
-162
-398
160
;

proc print noobs; run;
PG
Community Manager
Posts: 3,353

Re: -$160 instead of $-160

[ Edited ]

And if you need something more robust to apply a real currency format, you could test for different currency "unit" values and apply the proper formats according to the regional conventions.

 

data test;
 length unit $ 3 amount 8;
 infile datalines;
 input unit amount;
 datalines;
$ -162
$ -398
$ 160
GBP -162
GBP -398
GBP 160
€ -162
€ -398
€ 160
;
run;

data formatted;
 set test;
 select (unit);
  when ('$')  price = put(amount,dollar10.);
  when ('GBP')  price = put(amount,NLMNLGBP10.);
  when ('€')  price = put(amount,NLMNLEUR10.); /* or EURO10. */
  otherwise price=put(amount,dollar10.);
 end;
run;

 

fmtcurrency.png

 

(I don't know why the euro symbol isn't showing in records 7 and 8...but you get the idea, I hope.)

Esteemed Advisor
Posts: 5,396

Re: -$160 instead of $-160

Posted in reply to ChrisHemedinger

Strangely enough, I don't get the same result with my version of SAS (and/or locale)

 

                       Obs    unit    amount      price

                         1     $        -162          $-162
                         2     $        -398          $-398
                         3     $         160           $160
                         4     GBP      -162     (162,00 £)
                         5     GBP      -398     (398,00 £)
                         6     GBP       160      160,00 £
                         7     €        -162     (162,00 €)
                         8     €        -398     (398,00 €)
                         9     €         160      160,00 €
PG
Community Manager
Posts: 3,353

Re: -$160 instead of $-160

@PGStats  - the NL* formats are meant to be locale-sensitive, so if you're running in French Canadian, that might be different than my English-US.

☑ This topic is solved.

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

Discussion stats
  • 5 replies
  • 347 views
  • 2 likes
  • 4 in conversation