topic Rounding and Substringing variables within PROC SQL in SAS Studio

Rounding and Substringing variables within PROC SQL

HSM9 — Wed, 06 Dec 2017 01:00:40 GMT

I currently have this PROC SQL code:

TITLE "Subjects with High Blood Pressure (> 140 mmHg) (Ordered by Descending SBP)";
PROC SQL NUMBER;
SELECT StateCd LABEL = "State",
SSN LABEL = "Soc Sec #",
SBP LABEL = "Systolic BP",
BMI,
ICD10 LABEL = "ICD 10 Code"
FROM HypAnl.HypPrimAnl
WHERE SBP > 140
ORDER BY SBP DESC;
QUIT;

QUIT;

I have BMI variables that currently have one decimal that I want to be rounded to the nearest whole number. I also want my ICD.10 variable to go from the format B33.19 to just B33, so basically just a substring of this variable. I have been told to do this using formats even though my first thought was to use functions. Any thoughts on the best way to do this?

Re: Rounding and Substringing variables within PROC SQL

ChrisBrooks — Wed, 06 Dec 2017 01:19:02 GMT

It depends what you want - functions will change the actual value in the field whereas formats only change the way that value is displayed.

Re: Rounding and Substringing variables within PROC SQL

Shmuel — Wed, 06 Dec 2017 01:19:22 GMT

TITLE "Subjects with High Blood Pressure (> 140 mmHg) (Ordered by Descending SBP)";
PROC SQL NUMBER;
SELECT StateCd LABEL = "State",
SSN LABEL = "Soc Sec #",
SBP LABEL = "Systolic BP",
round(BMI, 1.0) as BMI,
scan(ICD10,1,'.') as ICD10 LABEL = "ICD 10 Code"
FROM HypAnl.HypPrimAnl
WHERE SBP > 140
ORDER BY SBP DESC;
QUIT;

Re: Rounding and Substringing variables within PROC SQL

HSM9 — Wed, 06 Dec 2017 01:21:43 GMT

That worked, thanks!

Re: Rounding and Substringing variables within PROC SQL

PGStats — Wed, 06 Dec 2017 01:26:29 GMT

Format for numbers, functions for strings:

TITLE "Subjects with High Blood Pressure (> 140 mmHg) (Ordered by Descending SBP)";
PROC SQL NUMBER;
SELECT  
    StateCd LABEL = "State",
    SSN LABEL = "Soc Sec #",
    SBP LABEL = "Systolic BP",
    BMI format=6.0,
    scan(ICD10, 1, ".") LABEL = "ICD 10 Code"
FROM HypAnl.HypPrimAnl
WHERE SBP > 140
ORDER BY SBP DESC;
QUIT;