Define SAS variable as Integer

[user24feb]

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

[LinusH · Accepted solution]

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.

[user24feb]

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;