macro and scientific notation

me55 — Wed, 26 Apr 2017 16:15:09 GMT

okay, so i imported a couple of tables and am building a table out of the data. the table is 5x5. so i have 4 columns that need to be summed and put into fields then i need to subtract those fields to fill in the third row. so after searching i found i could use macro variables to get the data inot variables and subtract them.. however, the column i am summing, even though i put the format importing as commaw.d, after summing is getting put into the table in **bleep** scientific notation which i cannot understand why since the columns are set up as decimal 20,2 which should be enough room for anything but it INSISTS!

so now when i subtract the variables, i get an error and cannot figure out how to get the values into numeric format so i can evaluate.

SAS Output

SOURCE COMMERCIAL_01 CONSTRUCTION_01 COMMERCIAL_02 CONSTRUCTION_02

Apr File	98452574	3185241	-256020	-5.679E7
Mar File	41736721	2.0938E8	-1.319E7	-3.514E7
Current Months File	56715853	.	.	.
Current Months S File	.	.	.	.
Difference	.	.	.	.

this is the table. i need to subtract the bottom from the top and put that in the third line below. here is the code...

data RPT01;
   set RPT01;
   CONSTRUCTION_01=input(CONSTRUCTION_XOFF,15.2);
   COMMERCIAL_02=input(COMMERCIAL_RECOV,15.2);
   CONSTUCTION_02=input(CONSTRUCTION_RECOV,15.2);
run;

%macro calcs01;
%local oper;
%do;

proc sql noprint;
select COMMERCIAL_01
   into :COMMXO01 - :COMMXO02
   from RPT01;
;

proc sql noprint;
select CONSTRUCTION_01
   into :CONSTXO01 - :CONSTXO02
   from RPT01;
;

proc sql noprint;
select COMMERCIAL_02
   into :COMMRE01 - :COMMRE02
   from RPT01;
;

proc sql noprint;
select CONSTRUCTION_02
   into :CONSTRE01 - :CONSTRE02
   from RPT01;
;

%let oper=-;

proc sql;
update RPT01
   set COMMERCIAL_01=%eval(&COMMXO01 &oper &COMMXO02)
   where SOURCE='Current Months GLFBS';
;

proc sql;
update RPT01
   set CONSTRUCTION_01=%eval(&CONSTXO01 &oper &CONSTXO02)
   where SOURCE like '%GLFBS';
;

proc sql;
update RPT01
   set COMMERCIAL_02=%eval(&COMMRE01 &oper &COMMRE02)
   where SOURCE like '%GLFBS';
;

%end;
%mend calcs01;
%run;

%calcs01

proc sql;
select *
   from RPT01;
;

so it works correctly in the first column and then fails for the others with this as an example...

NOTE 137-205: Line generated by the invoked macro "CALCS01".
90            proc sql; update RPT01 set COMMERCIAL_01=%eval(&COMMXO01 &oper &COMMXO02) where SOURCE='Current Months GLFBS'; ;
90       ! proc sql; update RPT01 set CONSTRUCTION_01=%eval(&CONSTXO01 &oper &CONSTXO02) where SOURCE like '%GLFBS'; ; proc sql
                                                                                                   ______
                                                                                                   22
90       ! ; update
ERROR 22-322: Syntax error, expecting one of the following: (, WHERE.

ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was:
       3185241 - 2.0938E8

i dont get this or how to fix it. i am using sas eg. any help would be very much appreciated. i have spent several hours and have reached a high level of frustration on this stupid problem.

thanks

Re: macro and scientific notation

Astounding — Wed, 26 Apr 2017 16:23:35 GMT

Switch from %EVAL to %SYSEVALF. %EVAL works with integers only, but %SYSEVALF can work with both decimal fractions and scientific notation.

Re: macro and scientific notation

me55 — Wed, 26 Apr 2017 17:10:14 GMT

thanks astounding. that worked perfectly. one further thing, why is the data being put into sci format and how can i put it into numeric format (integers, decimals)? everything works as is and i know i can pull using a format so it is not there but for my curiosity...

Re: macro and scientific notation

Astounding — Wed, 26 Apr 2017 17:19:12 GMT

I'm not sure this is the "why" for this particular case, but there is a general principle. If you assign a format to a variable, SAS goes out of its way to respect the width of the format. With a comma w.d format, SAS will do whatever it takes to express the value with a width of "w" (including using scientific notation of necessary).

topic Re: macro and scientific notation in SAS Programming

macro and scientific notation

Re: macro and scientific notation

Re: macro and scientific notation

Re: macro and scientific notation