converting the database formats in sas

[kajal_30]

Hi team,

I have database table with certain formats and I want to put the same sas format in final sas table so that it can be easily appended to the database table.

varchar(20) -->  char(20)

smallint -->  ?

decimat(5,2) --> ?

boolean --> ?

integer --> ?

date --> ?

Thanks 

Kajal

[Kurt_Bremser]

SAS has only two data types: character and numeric.

Boolean is numeric in SAS, with missing or 0 as false and everything elsee as true (logical operations in SAS result in either 1 or 0).

Dates are also numeric, they are counts of days with 1960-01-01 being day zero. A suitable display format makes them human-readable.

The SAS/ACCESS engine for the database should take care of a proper type and format assignment.

[Reeza · Accepted solution]

varchar(20) -->  $20.

smallint -->  8. (decimals required?)

decimat(5,2) -->   5.2

boolean -->  8.

integer --> 8. 

date --> Often a datetime is actually required, more details are needed.

[kajal_30]

Thank you @Reeza @Kurt_Bremser 

[Tom]

So a SAS dataset only has two data types. Fixed length character strings and floating point numbers.  Other than for the DATE/TIME/DATETIME values the format attached to the variable should not really matter.

So VARCHAR() types will be of type CHAR. Set the length to match.  If your system is storing UTF-8 codes (or other multi-bytes codes) AND it is counting the length in characters instead of bytes you might need a larger length in SAS to store the same 20 characters in UTF-8 as some characters take more than one byte.

The other variables in your list are numbers.  So define the variables as numbers in SAS.  You will have to be careful to not store values that are too large (or are not integers) to match the target type.  Note you could attach formats to display the numbers similarly on the SAS side, but it is the actual values that will be transferred and the format attached does not matter.

The DATE variable is a special case.  SAS will check the format attached to help it figure out what type of values you have in the SAS variable.  SAS stores dates as number of days.  It stores time as number of seconds since midnight. It store datetime as number of seconds.  Other systems might use the word "date" to mean any or all of those different types of numbers.