turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- General Programming
- /
- macro and scientific notation

Topic Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-26-2017 12:15 PM

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;

;

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

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to me55

04-26-2017 12:23 PM

All Replies

Solution

04-26-2017
01:08 PM

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to me55

04-26-2017 12:23 PM

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Astounding

04-26-2017 01:10 PM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to me55

04-26-2017 01:19 PM

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