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.
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;
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.
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;
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.