macro and scientific notation

Accepted Solution Solved
Reply
Contributor
Posts: 26
Accepted Solution

macro and scientific notation

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 File984525743185241-256020-5.679E7 
Mar File417367212.0938E8-1.319E7-3.514E7 
Current Months File56715853... 
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

Accepted Solutions
Solution
‎04-26-2017 01:08 PM
Super User
Posts: 5,498

Re: macro and scientific notation

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

View solution in original post


All Replies
Solution
‎04-26-2017 01:08 PM
Super User
Posts: 5,498

Re: macro and scientific notation

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

Contributor
Posts: 26

Re: macro and scientific notation

Posted in reply to Astounding

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

 

Super User
Posts: 5,498

Re: macro and scientific notation

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 141 views
  • 0 likes
  • 2 in conversation