Format numeric column with max length of 12 and 2 decimal places

[valentina2095]

I need to have one numeric column newvar with max length of 12 and 2 decimal places

PROC SQL;

   CREATE TABLE newtable AS

   SELECT

      (CASE
         WHEN oldvar=1 THEN oldvar2 * 123.45
         WHEN oldvar=2 THEN oldvar2
      END) newvar

   FROM oldtable;

QUIT;

I've tried with newvar format=12.2 and DECIMAL(oldvar * 123.45,12,2) with no success.

[RW9]

You are not creating a new dataset with that statement:

proc sql;
  create table want as
  select case when oldvar=1 then oldvar * 123.45
              when oldvar=2 then oldvar end as newvar format=12.2
  from   oldtable;
quit;
                     

In the table want, you will see newvar with the format applied.  Do note you have no else, so anything other than 1 or 2 will be ignored.

[ballardw · Accepted solution]

Describe what you mean by "no success"?

You do not need, or likely want parentheses around a case statement; any assignment in a SELECT statement should have AS varname so SQL knows what variable to assign the value to.

PROC SQL;
   SELECT
      CASE
         WHEN oldvar=1 THEN oldvar * 123.45
         WHEN oldvar=2 THEN oldvar
      END as newvar format=12.2
   FROM oldtable;
QUIT;

But the value 123.45 is not going to be much of a test for 12.2 format. A format is not necessarily going to pad the displayed length to 12 characters if the value uses fewer character positions.

Note that this shows that the format is applied and the decimal portion of the display only uses 2 decimal positions and rounds the result to fit:

PROC SQL;
   SELECT
      CASE
         WHEN oldvar=1 THEN oldvar * 123456.876543
         WHEN oldvar=2 THEN oldvar
      END as newvar format=12.2
   FROM oldtable;
QUIT;