Rounding the output with SAS DB2 SQL

[JC411911]

I am running db2 sql in SAS via a passthru and that is working fine. The issue I am having is getting my output to round. I have tried different options via format= but cant get desired results.

PROC SQL;
        CREATE TABLE WORK.ISSUES1671 AS  
        SELECT * FROM CONNECTION TO MYDB ( 

        SELECT COUNT(DISTINCT ISSUE_ID) AS ISSUE_ID,
            SUM(PAID_TO_DT_AMT) AS PAID_TO_DATE_YTD

        FROM LOD.RMD

            );
QUIT;

this gives me a table with this ISSUE_ID PAID_TO_DATE_YTD 3746 1015768311.9

I then have this:

PROC SQL;
    SELECT 
        sum(PAID_TO_DATE_YTD-843686339.02) AS PAIDOUT_YTD FORMAT= DOLLAR18.2
    FROM WORK.ISSUES1671;
QUIT;

which gives me this $172,081,972.83 which is correct but I want it to round to $172.1

I tried using the round function but it still gives me wonky results.

Any help or ideas on how I can get this to show?

[FreelanceReinh · Accepted solution]

Hello @JC411911,

Example 4: Creating a Picture Format for Large Dollar Amounts of the PROC FORMAT documentation explains how to create a picture format the second version of which comes very close to what you want. Can you try to adapt that format to your needs (in particular, add the ROUND option to the PICTURE statement)?

[JC411911]

Hello thank you for the suggestion I tried that but still don't get near what I am looking for. In fact the format was different then what I would expect using that option. 

proc format;   
   picture bigmoney (fuzz=0)
      1E06-<1000000000='0000 M' (prefix='$' mult=.000001);
	  value paidout;
run;

PROC SQL;
	SELECT 
		sum(PAID_TO_DATE_YTD-843686339.02) AS PAIDOUT_YTD format=paidout.
	FROM WORK.ISSUES1671;
QUIT;

172132494.79

PAIDOUT_YTD

[Reeza]

The format name is bigmoney, not paidout.

[JC411911]

@Reeza Thank you that worked I was under the assumption that the value was my format name. While it worked I would like it to round to one decimal place. I tried using the round function and then the format option but to no avail. Any ideas on how I can get it to round to 172.1 rather than just 172 

proc format;   
   picture bigmoney (fuzz=0)
      1E06-<1000000000='0000 M' (prefix='$' mult=.000001);
	  value paidout;
run;

PROC SQL;
	SELECT 
		round(sum(PAID_TO_DATE_YTD-843686339.02),.1) AS PAIDOUT_YTD format=bigmoney.
	FROM WORK.ISSUES1671;
QUIT;

$172 M

[ballardw]

When dealing with formats that use the MULT option you may have to provide the rounding because of the way the MULT result is overlaid onto the Picture.

Here is an example that extends the Picture, modifies the MULT value to match the new picture and an example using it.

Do note that range used for ROUND in this case is to the 100,000ths.

proc format;   
   picture bigmoney  (fuzz=0)
      1E06-<1000000000='0000.0 M' (prefix='$' mult=.00001);
	  value paidout;
run;

data example;
   x= 172092837;
   put x= bigmoney.;
   y=round(x,100000);
   put y= bigmoney.;
run;

The Log will show the results of applying the format. I know this is not the SQL but does demonstrate

[JC411911]

Thank you I was finally able to achieve the results I wanted using the below:

1proc format;   
   picture bigmoney (fuzz=0)
      1E06-<1000000000='00000.0 M' (prefix='$' mult=.00001);
run;

PROC SQL;
	SELECT 
		sum(PAID_TO_DATE_YTD-843686339.02) AS PAIDOUT_YTD format=bigmoney.
	FROM WORK.ISSUES1671;
QUIT;


$172.1M

[SASKiwi]

@JC411911 - You may also want to use the PICTURE format ROUND option as the default action is to truncate decimals.