topic Define SAS variable as Integer in SAS Data Management

Define SAS variable as Integer

user24feb — Mon, 12 Jan 2015 12:32:02 GMT

Hello,

I must upload data to a SQL server and created the table which contains the column definition and which is to be updated like this:

Libname stage ODBC Schema=ODBC DSN=sqlstage;

Proc SQL;

Create Table stage.Blend_xx (Scenario Char (4), Date Date, Alternative Integer, etc.);

Quit;

Am I right that it is no use to define Alternative's length in SAS and that I simply leave the integer-conversion to the access-interface?

Thanks&kind regards

Re: Define SAS variable as Integer

LinusH — Mon, 12 Jan 2015 13:39:59 GMT

No, I think you need to specify the data type for integers at least, I think the default data type for SAS numrical columns is NUMERIC.

Data Types for ODBC :: SAS/ACCESS(R) 9.4 for Relational Databases: Reference, Sixth Edition

Use the DBTYPE= ds option do override the default conversion.

Or create your table using explicit SQL pass-thru.

Re: Define SAS variable as Integer

user24feb — Mon, 12 Jan 2015 15:51:47 GMT

Works. Thanks!! (Would unmark your answer as helful and set it to right answer, but the homepage for some reasen doesn't let me. sorry.)

Sample program:

Data A;

Input Name $4. Number @@;
Datalines;
AAAA 1 BBBB 3 CCCC 7 DDDD 9 EEEE 6
;
Proc SQL;
Create Table stage.Int_Test (DBType=(Number=Integer)) ( Name Char(4), Number Integer ) ; * this is to define the data types;

Insert Into stage.Int_Test Select Name, Number As SQL_Integer From A; * this is the actual update;

Connect to ODBC (DSN=sqlstage); * this is to check which data types are actually assigned;
Create Table Int_Test_Info As Select * From Connection to ODBC (ODBC::SQLColumns (, , "INT_TEST" ));
Disconnect From ODBC;
Quit;