BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Brian_lewis67
Calcite | Level 5

In addition to the query I raised recently, someone has decided to amend all the date fields in several SQL databases from DateTime to Date.

 

Historically, we've read in the datetime fields and using datepart created a SAS date variable as follows:

 

format startdate ddmmyy10.;

 

startdate = datepart(start);

if startdate >= '01apr2016'd;

 

This no longer works. i'm using SAS 9.4 and have tried various methods to use the new date fields without success. I've read in a couple of places that SAS can't use Date format fields without using  ProcSQL

 

Is this true or can I modify the code above (it's used in a lot of programmes for many fields) with minimal change? I want to avoid break it up into individual characters and then concatenating to create a new field.

 

Thanks in advance.

 

Brian

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

First of all, make sure that the new date field is actually imported into SAS as date. Then omit the datepart conversion.

The column in SAS should be numeric and formatted with one of the date formats. And have values that make sense, of course.

If that is not the case, review the import process.

View solution in original post

12 REPLIES 12
Kurt_Bremser
Super User

First of all, make sure that the new date field is actually imported into SAS as date. Then omit the datepart conversion.

The column in SAS should be numeric and formatted with one of the date formats. And have values that make sense, of course.

If that is not the case, review the import process.

Brian_lewis67
Calcite | Level 5

Thanks for pointing me in the right direction. It atually reads the date in as text $10. so I've used the input function.

 

startdate = input(start,yymmdd10.)

 

and this appears to have worked. Should this cause any issues when then comparing two dates?

 

Thanks,

 

Brian

 

 

Patrick
Opal | Level 21

When interacting with a Database you should try and get to a level where you understand what @LinusH is talking about.

 

The following link provides you the information to what data type and format the SAS Access engine converts SQL Server data types when pulling the data into SAS.

http://support.sas.com/documentation/cdl/en/acreldb/69039/HTML/default/viewer.htm#n0h4i25zq3t58en1lg...

 

That you end up with a SAS character variable of length 10 strongly indicates that the "date" in SQL Server is now actually stored as a string  and most likely using a SQL Server variable of data type SQL_VARCHAR or SQL_CHAR.

 

Brian_lewis67
Calcite | Level 5

Hi Patrick,

 

Thanks for the link. That certainly explains how data types are treated when reading them in from an SQL database.

 

Unfortunately I'm not an expert at SQL or SAS but have been tasked with updating programme my team use as a result of server and Database changes.

 

It was a case of we are making these changes without consultation with users and offer no support either.

 

Regards,

 

Brian

Kurt_Bremser
Super User

@Brian_lewis67 wrote:

Thanks for pointing me in the right direction. It atually reads the date in as text $10. so I've used the input function.

 

startdate = input(start,yymmdd10.)

 

and this appears to have worked. Should this cause any issues when then comparing two dates?

 

Thanks,

 

Brian

 

 


Just to be sure, apply a date format to startdate.

format startdate date9.;

This displays the date in human-readable format and allows you to verify that the values are correct. All comparisons will then work as expected.

 

Since I read database data not from the database itself, but from flat files unloaded from a database snapshot, I always get date values as CHAR(10) and have to read into a temprorary variable and then convert by using the input() function.

LinusH
Tourmaline | Level 20

As in many ODBC (or are you using OLEDB?) trouble shooting situations, accessing the same data from another application is one way to understand the issue.

According to the doc, the SQL_TYPE_DATE will be interpreted as date in SAS.

If your date columns is categorized in ODBC as SQL_TYPE_TIMESTAMP, try use the DBTYPE= data set option.

Data never sleeps
Brian_lewis67
Calcite | Level 5
Apologies Linus but most of that makes any sense given my very, very limited knowledge. Thanks anyway.
LinusH
Tourmaline | Level 20
No problem, and sorry for being ignorant about your knowledge level. I'm glad that @Patrick chimed in and saved the day 😎
Data never sleeps
Brian_lewis67
Calcite | Level 5

There is no need to apologise. Thanks for your input. 

bnawrocki
Quartz | Level 8

I think I have the same problem. We run SAS code to import our month-end SQL Server database tables into SAS datasets. There are dozens of tables, with code such as this:

PROC SQL dquote=ansi ;
           create table dd_admin_trail as
              (select * from  ncedss.dd_admin_trail);
              quit;

In September, all date fields in SQL Server were stored as datetime fields, and imported into SAS correctly as datetime fields. In October, a change was made to SQL Server - we upgraded to SQL Server 2008 and all date fields are now stored as "DATE" fields (I checked in SQL Server, and the fields have a "DATE" type), but they import into SAS as CHAR fields of format/length $10, which are not easy to work with in SAS code.

 

 

How can I get SAS to import these DATE fields in SQL Server into numeric date fields in SAS? Shouldn't that just happen by default?

 

I found a way to force SAS to do this, by hard-coding a couple of date fields like in this code, but there are dozens of date fields across dozens of SQL Server tables, and I don't want to write this DBSASTYPE clause for EVERY one of them!

PROC SQL;
   create table DD_ADMIN_TRAIL as
     select * from  ncedss.DD_ADMIN_TRAIL (dbsastype=(ASSIGNED_TO_DT='DATE' CDC_BASE_DT='DATE') );
quit;
Patrick
Opal | Level 21

Please create a new question for a new problem and just post a reference to similar questions/solutions you found.

 

It's likely that you're using the wrong ODBC driver, which is something your SAS Admin/SAS Installer would have to change.

http://support.sas.com/kb/40/080.html

 

 

 

bnawrocki
Quartz | Level 8

Thanks, Patrick!  I should have searched the SAS Usage Notes first, before posting here. Yes, I am using the "SAS/ACCESS Interface to OLE DB" driver to access SQL Server data from SAS code.

 

After reading the link you provided, it sounds like that should fix my problem. I'll try it out next week, when everyone is back in the office after Thanksgiving break.

 

UPDATE: It worked! Based on a similar Usage Note for SAS/ACCESS Interface to OLE DB  - http://support.sas.com/kb/39/188.html -- we just changed our PROVIDER= parameter to Provider=SQLNCLI10.1 in our LIBNAME xxx OLEDB ...; statement, and the date fields are now downloading correctly from SQL Server 2008 into SAS numeric date variables.

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
  • 12 replies
  • 18503 views
  • 1 like
  • 5 in conversation