Help using Base SAS procedures

Data types - moving data to SQL Server

Reply
N/A
Posts: 0

Data types - moving data to SQL Server

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?
Frequent Contributor
Posts: 139

Re: Data types - moving data to SQL Server

Posted in reply to deleted_user
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.
N/A
Posts: 0

Re: Data types - moving data to SQL Server

Posted in reply to deleted_user
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?
Valued Guide
Posts: 2,177

Re: Data types - moving data to SQL Server

Posted in reply to deleted_user
TRIM(LEFT( YOUR_STRING ))
Frequent Contributor
Posts: 139

Re: Data types - moving data to SQL Server

Posted in reply to deleted_user
see the STRIP function strip(mystring)
Ask a Question
Discussion stats
  • 4 replies
  • 184 views
  • 0 likes
  • 3 in conversation