topic Sum 2 macro variables with format in SAS Programming

Sum 2 macro variables with format

SASdevAnneMarie — Wed, 15 Feb 2023 10:11:41 GMT

Hello Experts,

Do you know, please, how to to sum 2 macro variables with € format ? For exemple, if I have in &TOTAL_S 1 € and in &TOTAL2_Another_2 3 €, the sum in &TOTAL_SUP2 must be 4 €.

In case when the table is empty (no observation), how to create the macro variable with 0 value ?

My code is :

options locale=FR;

data matable_bis;
	set matable (obs=1);
	TOTAL2=propcase((put(TOTAL,NLMNLEUR12.0)));
	keep TOTAL TOTAL2;
run;

/***if my table matableenPlus2 is empty (no observation),
how to create the macrovariable ? ***/

data matableenPlus2;
	set matableenPlus;
	TOTAL2_Another=propcase((put(Another_total,NLMNLEUR12.0)));
	keep TOTAL2_Another;
run;


proc sql noprint;
	select TOTAL2 into : TOTAL_S from matable_bis;
quit;

proc sql noprint;
	select TOTAL2_Another into : TOTAL2_Another_S from matableenPlus2;
quit;

/****How to sum 2 macro variables with format ? ***/
%let TOTAL_SUPP2 = %sysfunc(&TOTAL_S., &TOTAL2_Another_S.);

Thank you for your help !

Re: Sum 2 macro variables with format

PeterClemmensen — Wed, 15 Feb 2023 09:59:12 GMT

Can you post an example of what the two macro variables look like?

Re: Sum 2 macro variables with format

Kurt_Bremser — Wed, 15 Feb 2023 10:08:32 GMT

Why do you go to such lengths to store the values as character with a currency format when you later need the raw numeric values?

Anyway, you can always use the original variables in the SELECT INTO without a format.

Which value do you want in case there's no observations? Zero or missing?

Re: Sum 2 macro variables with format

SASdevAnneMarie — Wed, 15 Feb 2023 10:17:09 GMT

Thank you for your answer.

I specify : I would like to improve my code : I need to sum another variable.

So, I have the code that calculate the total, after I put the total in € format.

Now, I need to add to this total another total, I'm wondering if I can sum 2 macro variables.

Another problem : when I calculat another total, sometimes I can have no values in the tables (empty table), in this case I woul like to add 0.

Re: Sum 2 macro variables with format

SASdevAnneMarie — Wed, 15 Feb 2023 10:21:49 GMT

Thank you for the message, I added the information to the message.

Re: Sum 2 macro variables with format

PaigeMiller — Wed, 15 Feb 2023 11:14:15 GMT

Repeating what has already been said:

You have:

data matable_bis;
	set matable (obs=1);
	TOTAL2=propcase((put(TOTAL,NLMNLEUR12.0)));
	keep TOTAL TOTAL2;
run;

/***if my table matableenPlus2 is empty (no observation),
how to create the macrovariable ? ***/

data matableenPlus2;
	set matableenPlus;
	TOTAL2_Another=propcase((put(Another_total,NLMNLEUR12.0)));
	keep TOTAL2_Another;
run;

Do the sum on data set variables TOTAL and ANOTHER_TOTAL, do not sum macro variables that have characters in them.

Re: Sum 2 macro variables with format

Kurt_Bremser — Wed, 15 Feb 2023 18:05:31 GMT

Of course you can sum macro variables, if they contain numbers in a programming sense (only digits, a decimal dot, and possibly a leading sign). That's why you MUST use the unformatted values.

Re: Sum 2 macro variables with format

ballardw — Wed, 15 Feb 2023 18:46:24 GMT

You run into 2 problems with that format. One is that the COMMA that may get placed in the value (national language settings) means that would be treated as a delimiter by the macro language. Then just having the currency symbol means that the result is not treated as a number even with the proper function calls.

Example using US currency as but Euros will do the same. Note the conversion note only shows PART of the VAL1 in the error message. That is because of the comma in the value.

80   %let val1 = $123,456.77;
81   %let val2 = $45.12;
82
83   %let sum= %sysevalf(&val1. + &val2.);
ERROR: Unknown %SYSEVALF conversion operand '456.77 + $45.12' specified; conversion is terminated.
ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric
       operand is required. The condition was: $123
84   /* error so no value*/
85
86
87   %let val11 = 123456.77;
88   %let val22 = 45.12;
89
90   %let sum2= %sysevalf(&val11. + &val22.);
91   %put sum is: &sum2.;
sum is: 123501.89

%sysfunc is going to require at least one data step function, such as SUM, but still will fail because of the currency symbols.