topic Data types - moving data to SQL Server in SAS Procedures

Data types - moving data to SQL Server

deleted_user — Mon, 07 Jun 2010 14:42:10 GMT

I have many SAS data files, that I need to move to SQL Server.
The date and time fields keep erroring out.
I am doing a simple
insert into table (fields)
select fields from SAS table
for the date fields I have tried put (dob, date10.) as dob - and that errors out with a Conversion Failed when converting date and /or time from character string.
Same thing with time fields - I tried put (collecttime, time8.) as time
The data in SAS are formatted as Number 5 YYMMDDN8
and Number 5 Time8.
The data I want in SQL Server 2008 would be a time(7) for the times and date for the dates.

Any recommendations?

Re: Data types - moving data to SQL Server

darrylovia — Mon, 07 Jun 2010 15:04:38 GMT

To work around your issue, I would convert the sas dates to sas datetime using the dhms function and datetime21.2 format. Insert the new datetime field into SQL Server. Then use the SQL update statement to parse out the date from the time in SQL Server. This would be a pain but it should work.

Re: Data types - moving data to SQL Server

deleted_user — Tue, 08 Jun 2010 14:48:27 GMT

I ended up doing a really crappy case statement:
case when dob <>. then put(dob, mmddyy10.) end as dob
this is really crappy syntax, but works!

Does anyone know how to do a Ltrim(rtrim( )) to get rid of extra spaces?

Re: Data types - moving data to SQL Server

Peter_C — Wed, 09 Jun 2010 16:32:49 GMT

TRIM(LEFT( YOUR_STRING ))

Re: Data types - moving data to SQL Server

darrylovia — Wed, 09 Jun 2010 17:23:32 GMT

see the STRIP function strip(mystring)