SQL to SAS Date Formatting Character to Numeric

[dsriggs]

How can I convert a data variable that comes in to SAS from SQL as a character with length 11 and $11 format into a numeric variable with the Date9. format?  Is there a way I can do the formatting in the Proc SQL step instead of needing an additional data step?  

[PeterClemmensen]

You can do it directly in SQL. Post what you have now?

[dsriggs]

proc sql;
connect to odbc(noprompt = "Driver={SQL Server};
                            Server=server;
                            Database=database;");
create table sastable as
select * from   connection to odbc
(
select convert(varchar(11),cast(DATEADD(MONTH,DATEDIFF(month,0,datevar),0) as DATE),106) as datevar,
sum(case when COL='Name' then Volume else 0 end) as 'Name'
from SQLTABLE
where OtherDate=(select MAX(OtherDate) from SQLTABLE)
group by datevar
Order by datevar);
quit;

Here is a generic form of the Proc SQL statement I am working with.  The date formatting in the Select statement is so complicated because I have daily volumes that I am trying to group by month, and then set the date as the first of the month, but then the formatting comes into SAS as a character variable.

[LinusH]

I'm not an SQL Server specialist, but don't you already do the conversion in that step?

But you can do it on the SAS side as well, just use input([charvar], [date format that matches the on in the car variable]) in the select.