topic Re: CONVERT SQL SERVER CODE TO SAS in New SAS User

CONVERT SQL SERVER CODE TO SAS

alepage — Wed, 26 Sep 2018 17:46:57 GMT

Hello,

Here's an example of the SQL code I am dealing with. (The variable name has been changed for security purpose)

CREATE TABLE "test"
( "var1" NUMBER(12,0) NOT NULL ENABLE,
"var2" NUMBER(2,0) NOT NULL ENABLE,
"var3" NUMBER(10,0),
"var4" NUMBER(3,0),
"var5" NUMBER(15,2),
"var6" NUMBER(15,2),
"var7" NUMBER(15,2),
);

I have consulted the following page: http://support.sas.com/documentation/cdl/en/sqlproc/63043/HTML/default/viewer.htm#n0v236a0ti4nmen1mmw4gexjd0tg.htm

as I have understood from my reading, I have transformed this code into SAS code as below:

Proc Sql;

CREATE TABLE test

( var1 numeric format = 12.0 not null,

var2 numeric format =2.0 NOT NULL,

var3 numeric format =10.0,

var4 numeric format =3.0,

var5 numeric format =15.2,

var6 numeric format =15.2,

var7 numeric format =15.2

);

Quit;

Is it the similar SAS format or close to the SQL format?

Thanks for your help

Re: CONVERT SQL SERVER CODE TO SAS

LinusH — Thu, 27 Sep 2018 01:26:55 GMT

"Close" is a matter of opinion. But they are mot the same.
While databases use this to specifiy precision in storage, SAS formats only specify the default display. Precision in is only managed by the column length.
Also the numbers in the format have slightly different meaning. The first number covers the full length of the displayed value, including any signs, separators and decimals.

Re: CONVERT SQL SERVER CODE TO SAS

alepage — Thu, 27 Sep 2018 12:29:50 GMT

Hello,

Regarding the column length, if a numerical variable allowed only two digits, what will be the column length?

if the numerical variable permits 10 digits, what will be the column length?

Thanks in advance for your help.

Re: CONVERT SQL SERVER CODE TO SAS

ChrisHemedinger — Thu, 27 Sep 2018 12:35:53 GMT

By default, SAS numeric variables have a length of 8 (bytes). There usually is not a reason to change that unless you have some strict requirements for storage.

For precision in reporting and for classification in analytics, you can use SAS formats to control display precision. Read this article about the difference between length and format in SAS.