BookmarkSubscribeRSS Feed
MikePHFEWIC
Calcite | Level 5
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?
5 REPLIES 5
Bill
Quartz | Level 8
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
Calcite | Level 5
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
Quartz | Level 8
Mike;

You're right, this shouldn't be difficult. Please post an actual date string as an example.
LAP
Quartz | Level 8 LAP
Quartz | Level 8
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
Quartz | Level 8 LAP
Quartz | Level 8
Sorry - I meant to convert your example in my answer....


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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 2774 views
  • 1 like
  • 3 in conversation