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
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;
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;
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;
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.)
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 €
@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.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.