BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
me55
Quartz | Level 8

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
1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

3 REPLIES 3
Astounding
PROC Star

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

me55
Quartz | Level 8

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

 

Astounding
PROC Star

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

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