topic Re: Executing SQL from Visual Studio against SAS datasets in SAS Programming

Executing SQL from Visual Studio against SAS datasets

buckeye — Mon, 27 Jun 2011 20:46:18 GMT

Hi all,

How to set up an enviroment in MS Visual Studio so we can run SQL command from MS Visual Studio against SAS datasets?

Thanks very much in advance!

Re: Executing SQL from Visual Studio against SAS datasets

chang_y_chung_hotmail_com — Wed, 29 Jun 2011 15:16:45 GMT

Establish a connection via IOM provider.

Executing SQL from Visual Studio against SAS datasets

buckeye — Thu, 30 Jun 2011 16:14:50 GMT

Thank you very much, chang_y_chung. The link provides very helpful infomation. It turns out that we don't currently have license (Integration Technologies) to access SAS data files from outside SAS. But we do currently have license (SAS/Access) to access SQL Server from a SAS program. I now have an issue dealing with SQL Server datetime type field.

The column as SAS sees is (InvoiceDate is a datetime type defined in SQL Server):

InvoiceDate Num 8 DATETIME22.3 DATETIME22.3 InvoiceDate

The field in SAS data file is:

INVCDATE Num 8 MMDDYY10. INVOICE DATE (SAS)

But when I tried to insert the record into SQL Server from SAS data file, I got data type mismatch error or inserted incorrectly (showing 1960-01-01 xxxxxx). Does anybody have a solution to this?

Thanks in advance!

Executing SQL from Visual Studio against SAS datasets

mojerry2 — Tue, 05 Jul 2011 06:29:03 GMT

an example about inserting datetime into sql server :

%let nowdate = %sysfunc(today(),date7.);

%let nowtime = %sysfunc( time(),time5.);

proc sql;

insert into AAA.BBB (Current Date&time)
VALUES ("&nowdate &nowtime");

quit;

Don't forget if you're trying to write a datetime from a mmddyy10 format you have to give him hhmmss.

it's the trick in sas between a date format and a datetime.

why not write into your sql server this function : dhms(invcdate,0,0,0)

Executing SQL from Visual Studio against SAS datasets

buckeye — Tue, 05 Jul 2011 13:09:33 GMT

It works beautifully. Thank you very much mojerry2!

Executing SQL from Visual Studio against SAS datasets

mojerry2 — Tue, 05 Jul 2011 13:40:35 GMT

you're welcome