topic Re: -$160 instead of $-160 in SAS Programming

-$160 instead of $-160

subrat1 — Tue, 15 Aug 2017 18:24:43 GMT

I have two column :

UNIT	Amount
$	-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

Re: -$160 instead of $-160

kiranv_ — Tue, 15 Aug 2017 18:43:05 GMT

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;

Re: -$160 instead of $-160

PGStats — Tue, 15 Aug 2017 18:47:39 GMT

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;

Re: -$160 instead of $-160

ChrisHemedinger — Tue, 15 Aug 2017 18:54:23 GMT

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;

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

Re: -$160 instead of $-160

PGStats — Tue, 15 Aug 2017 19:35:04 GMT

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 €

Re: -$160 instead of $-160

ChrisHemedinger — Tue, 15 Aug 2017 19:53:41 GMT

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