BookmarkSubscribeRSS Feed
akhilesh_joshi
Fluorite | Level 6

Hi,

 

I am trying to export my SAS data sets to SQL Server. There are 3 date columns in my table. However, I am facing a data type mismatch issue. I have created an empty table with all required columns on SQL Server. The Date columns on SQL Server have the datatype Date. Please find below my code to transfer SAS data to SQL Server:

 

proc sql;

Connect to odbc(datasrc="data" user=Writer password=Writer);

INSERT INTO SQL.Database

SELECT *

FROM SAS.Database

WHERE ID IS NOT NULL;

disconnect from odbc;

Quit;

 

Please find code I used on SQL server to create blank table (there are more columns than these three):

 

CREATE TABLE database

(

START_DATE Date,

END_DATE Date,

REPORT_DATE Date,

);

 

The format and informat of SAS Date columns is MMDYY10. How do I move data from SAS Date columns to the three Date columns on SQL Server?

 

 

5 REPLIES 5
Reeza
Super User

I'm assuming you've already searched and tried the standard solutions. 

 

So what happens if you use Date9 or a datetime type/format instead?

akhilesh_joshi
Fluorite | Level 6

Hi Reeza,

 

The SAS dataset already has MMDDYY10. format. How do I convert it to Date9. or a datetime format?

Reeza
Super User

Apply a format...

 

That code you showed wouldn't work, because when you use SQL pass through you don't have access to your SAS Database.

 

Format variable_name date9.;

 

akhilesh_joshi
Fluorite | Level 6

Thanks Reeza. I would definitely give it a try and let you know!

 

SASKiwi
PROC Star

Here's the documentation explaining the default data type translations:

 

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

 

What is the mismatch you are getting?

 

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 connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 5 replies
  • 1688 views
  • 0 likes
  • 3 in conversation