topic Re: Reserved characters in SAS Programming

Reserved characters

deleted_user — Wed, 22 Apr 2009 10:35:22 GMT

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 &num;

%mend local_curr;
%local_curr (port_id=1);
%local_curr (port_id=7);
%local_curr (port_id=8);

Re: Reserved characters

DanielSantos — Wed, 22 Apr 2009 10:51:43 GMT

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.

Re: Reserved characters

data_null__ — Wed, 22 Apr 2009 11:28:36 GMT

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]

Re: Reserved characters

deleted_user — Wed, 22 Apr 2009 11:55:47 GMT

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 &num;
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

Re: Reserved characters

data_null__ — Wed, 22 Apr 2009 13:13:21 GMT

> 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]

Re: Reserved characters

DanielSantos — Wed, 22 Apr 2009 16:39:48 GMT

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.

Re: Reserved characters

deleted_user — Thu, 23 Apr 2009 07:21:36 GMT

Thanks Daniel.