BookmarkSubscribeRSS Feed
deleted_user
Not applicable
Hiya

I am trying to use tagattr to apply formats to currency in a report I am creating. The problem is that I am using a macro to switch currencies which means that I have to put the Excel formats into a macro variable.

The format strings are [$€-1809]#,##0.00 and [$$-409]#,##0.00 but SAS just gives me an error as it does not like the [ or $, or the use of %sysfunc. I am using call symput to do this. I have tried to put the formats into a variable using %let before using the call symput but this does not help.

I have searched the forums but cannot find anything related to special characters. I have tried using %nrbquotes. My code is below.

%macro local_curr(port_id=);

data _null_;
if &port_id = 1 or &port_id = 26 then prefix="currency";
else if &port_id = 5 or &port_id = 49 or &port_id = 7 then prefix=%nrbquote([$€-1809]#,##0.00);
else if &port_id = 8 then prefix=%nrbquote([$$-409]#,##0.00);
call symput("prefix",%sysfunc(compress(prefix)));
run;

%put Local Currency is &prefix;

data _null_;
call symput("loc",%sysfunc(compress(&prefix)));
call symput("GBP",%sysfunc(compress("currency")));
call symput("num",%sysfunc(compress("###,###,###")));
run;

%put local is &loc, GBP is &GBP and number is #

%mend local_curr;
%local_curr (port_id=1);
%local_curr (port_id=7);
%local_curr (port_id=8);
6 REPLIES 6
DanielSantos
Barite | Level 11
Hello.

Not sure about what you're trying to do, but nothing wrong about the [ and $ char, and there's something wrong about your code.

When mixing dataset vars (prefix) with macro expressions that resolves to a char value you should enclose the expression in double quotes.
Another common mistake is not defining the maximum length of a new char var inside the datastep. SAS will do it for you, on assumption. In this case SAS is assuming that the first assignment to the prefix var (prefix="currency") will be its maximum allocated size, so prefix is defined in your example as a 8 char var. Keeping that in mind, the expression "[$€-1809]#,##0.00" will be truncated to "[$€-1809".

So,

data _null_;
if &port_id = 1 or &port_id = 26 then prefix="currency";
else if &port_id = 5 or &port_id = 49 or &port_id = 7 then prefix=%nrbquote([$€-1809]#,##0.00);
else if &port_id = 8 then prefix=%nrbquote([$$-409]#,##0.00);
call symput("prefix",%sysfunc(compress(prefix)));
run;

Should be:

data _null_;
length prefix $200; /* define prefix size */
if &port_id = 1 or &port_id = 26 then prefix="currency";
else if &port_id = 5 or &port_id = 49 or &port_id = 7 then prefix="[$€-1809]#,##0.00"; /* double quotes needed, no need for %nrbquote */
else if &port_id = 8 then prefix="[$$-409]#,##0.00"; /* same here */
call symput("prefix",compress(prefix)); /* no need for %sysfunc here */
run;

Is this what you're trying to do?

Greetings from Portugal.

Daniel Santos at www.cgd.pt.
data_null__
Jade | Level 19
I would suggest a format. If you want to resolve it in code use

%sysfunc(putN(24,portid))

[pre]
proc format;
value portid
1,26 = 'currency'
5,7,49 = '[$€-1809]#,##0.00'
8 = '[$$-409]#,##0.00'
;
run;

data _null_;
Put 'NOTE: PORTID formatted' @;
do portid = 1,26,5,7,49,8;
put (portid portid) (/ 'NOTE- ' 3. +2 :portid.) @;
end;
run;
%let portid = 26;
%put NOTE: PORTID &portid is %sysfunc(putN(&portid,portid));
[/pre]
deleted_user
Not applicable
Thanks for the answers guys.

data _null_: When you put numbers through to Excel they must be unformatted or else Excel gets confused and makes a mess. You use style={tagattr="format:currency"} to tell Excel how to format the data once it is there.

Daniel: I have swapped your code for mine which has resolved one of the problems but I am still getting an error in my log, as below.

99 %macro local_curr(port_id=);
100
101 data _null_;
102 length prefix $200; /* define prefix size */
103 if &port_id = 1 or &port_id = 26 then prefix="currency";
104 else if &port_id = 5 or &port_id = 49 or &port_id = 7 then
104! prefix="[$€-1809]#,##0.00";
105 /* double quotes needed, no need for %nrbquote */
106 else if &port_id = 8 then prefix="[$$-409]#,##0.00"; /* same here */
107 call symput("prefix",compress(prefix)); /* no need for %sysfunc here */
108 run;
109
110 %put Local Currency is &prefix;
111
112 data _null_;
113 call symput("loc",&prefix);
114 call symput("GBP","currency");
115 call symput("num","###,###,###");
116 run;
117
118 %put local is &loc, GBP is &GBP and number is #
119
120 %mend local_curr;
121 %local_curr (port_id=1);

NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds


Local Currency is currency

NOTE: Numeric values have been converted to character values at the places given by:
(Line):(Column).
1:1
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds


local is ., GBP is currency and number is ###,###,###
122 %local_curr (port_id=7);

NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds


Local Currency is [$€-1809]#,##0.00

NOTE: The SAS System stopped processing this step because of errors.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

NOTE: Line generated by the macro variable "PREFIX".
[$€-1809]#,##0.00
-
386
76
-
200

ERROR 386-185: Expecting an arithmetic expression.

ERROR 76-322: Syntax error, statement will be ignored.

ERROR 200-322: The symbol is not recognized and will be ignored.


WARNING: Apparent symbolic reference LOC not resolved.
WARNING: Apparent symbolic reference GBP not resolved.
WARNING: Apparent symbolic reference NUM not resolved.
local is &loc, GBP is &GBP and number is &num
123 %local_curr (port_id=8);

NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds


Local Currency is [$$-409]#,##0.00

NOTE: The SAS System stopped processing this step because of errors.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

NOTE: Line generated by the macro variable "PREFIX".
[$$-409]#,##0.00
-
386
76
-
200

ERROR 386-185: Expecting an arithmetic expression.

ERROR 76-322: Syntax error, statement will be ignored.

ERROR 200-322: The symbol is not recognized and will be ignored.


WARNING: Apparent symbolic reference LOC not resolved.
WARNING: Apparent symbolic reference GBP not resolved.
WARNING: Apparent symbolic reference NUM not resolved.
local is &loc, GBP is &GBP and number is &num
data_null__
Jade | Level 19
> data _null_: When you put numbers through to Excel
> they must be unformatted or else Excel gets confused
> and makes a mess. You use
> style={tagattr="format:currency"} to tell
> Excel how to format the data once it is there.

I understand that Scoob. My suggestion was to replace the data step that creates the macro variables you don't need and simply use %sysfunc(putN(...). Value label formats provide a nice simple table lookup feature.

[pre]
style={tagattr="format:%sysfunc(putN(&port_id,portid))"}
[/pre]
DanielSantos
Barite | Level 11
Hello,

Sorry, I've just focused on the first datastep. You should use double quotes for the resolved value of the macro var on your last datastep.

data _null_;
call symput("loc","&prefix"); /* &prefix in double quotes */
call symput("GBP","currency");
call symput("num","###,###,###");
run;

Greetings from Portugal.

Daniel Santos at www.cgd.pt.
deleted_user
Not applicable
Thanks Daniel.

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
  • 6 replies
  • 891 views
  • 0 likes
  • 3 in conversation