BookmarkSubscribeRSS Feed
buckeye
Obsidian | Level 7

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!

5 REPLIES 5
buckeye
Obsidian | Level 7

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!

mojerry2
Fluorite | Level 6

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)

buckeye
Obsidian | Level 7

It works beautifully. Thank you very much mojerry2!

mojerry2
Fluorite | Level 6

you're welcome Smiley Wink

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
  • 1226 views
  • 6 likes
  • 3 in conversation