BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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?
4 REPLIES 4
darrylovia
Quartz | Level 8
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.
deleted_user
Not applicable
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?
Peter_C
Rhodochrosite | Level 12
TRIM(LEFT( YOUR_STRING ))
darrylovia
Quartz | Level 8
see the STRIP function strip(mystring)

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 4 replies
  • 866 views
  • 0 likes
  • 3 in conversation