PROC SQL with Dates from database

[MikePHFEWIC]

I have date values in my SQL Server database and am accessing them through ACCESS ODBC. Accessing them works fine but the dates come back as character values which I can't use. How do I get them into a format where I can use them for date math and date functions in a DATA or other PROC step?

PROC SQL;
CREATE TABLE mikey
SELECT NAME,DOB,Enrolled from SQLServer.EnrollmentTable;

I do a libdef to point the SQLServer library to my sqlserver database. It all works fine and the data comes back. The problem is that the DOB (date of birth) and enrolled (date of enrollment) come back as strings of format yyy-mm-dd 00:00:00

I do get data back but then can't work with these two variables as dates. The format seemed to be YMDDTTM24. but it doesn't find it. I am using SAS/PC 9.2.

I have tried several things, including the following, but nothing works...... Do you ever get dates from DB2 or SQL or Orable? I am using ACCESS for ODBC.

SELECT NAME,INPUT(DOB,date9.) as DOB,Enrolled from SQLServer.EnrollmentTable;

SELECT NAME,DOB INFORMAT=YMDDTTM24. ,Enrolled from SQLServer.EnrollmentTable;

Help! How can I use these dates?

[Bill]

I frequently get date data back from Oracle. In my case it comes back as a datetime value that I need to parse out into date and/or time variables in a datastep following the SQL step to turn the Oracle date value into a SAS date value.

data new;
set OracleDataView;
SASDate=datepart(OracleDateTimeValue);
SASTime=timepart(OracleDateTimeValue);**Optional;
run;

Now I can use SASDate in any number of ways.

wd

[MikePHFEWIC]

You are fortunate. The SAS saleslady talked me into getting ACCESS for ODBC instead of ACCESS for SQL Server and now my dates come back as strings. I have spent all day trying to figure out how to use the darn things. I can't believe this is so difficult.
Obviously it won't be difficult once I get one stinking little example, but I can't find it.

My latest guess was to read in all the data, just accept the text. Then try to get it into Date variables with the following.... it doesn't work.

DATA CalcMade;
SET mylib.PullBFEX;
format DOBDate date7.;
DOBDate = input( dob , YMDDTTM24.);

ExDate = input( ExEnd , YMDDTTM24.);
format ExDate date7.;
ComboDate = input( ComboEnd , YMDDTTM24.);
format ComboDate date7.;

ExMonths = INTCK('MONTH' ,DOBDate,ExDate);
ComboMonths = INTCK('MONTH' ,DOBDate,ComboDate);

[Bill]

Mike;

You're right, this shouldn't be difficult. Please post an actual date string as an example.

[LAP]

I think that you need to use the SASDATEFMT option. See

http://support.sas.com/documentation/cdl/en/acreldb/61890/HTML/default/a001371624.htm

Use the SASDATEFMT= option to prevent date type mismatches in these circumstances:

during input operations to convert DBMS date values to the correct SAS DATE, TIME, or DATETIME values

during output operations to convert SAS DATE, TIME, or DATETIME values to the correct DBMS date values.

I've encountered a similiar problem when writing to oracle tables

Here's an example

Insert into xxx.xxx(sasdatefmt=(GATE_RCVD_DATE='date9.'))

Hope this helps

[LAP]

Sorry - I meant to convert your example in my answer....


SELECT NAME,DOB,Enrolled from
SQLServer.EnrollmentTable(sasdatefmt=(DOB='date9.' Enrolled='date9.'));