topic Format numeric column with max length of 12 and 2 decimal places in SAS Studio

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

valentina2095 — Wed, 19 Sep 2018 15:08:38 GMT

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.

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

RW9 — Wed, 19 Sep 2018 14:47:19 GMT

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.

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

ballardw — Wed, 19 Sep 2018 14:49:49 GMT

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;