BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
subrat1
Fluorite | Level 6

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

5 REPLIES 5
kiranv_
Rhodochrosite | Level 12

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;
PGStats
Opal | Level 21

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
ChrisHemedinger
Community Manager

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

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
PGStats
Opal | Level 21

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
ChrisHemedinger
Community Manager

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

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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