topic Re: SQL to SAS Date Formatting Character to Numeric in SAS Programming

SQL to SAS Date Formatting Character to Numeric

dsriggs — Wed, 24 May 2017 12:41:49 GMT

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?

Re: SQL to SAS Date Formatting Character to Numeric

PeterClemmensen — Wed, 24 May 2017 12:43:35 GMT

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

Re: SQL to SAS Date Formatting Character to Numeric

dsriggs — Wed, 24 May 2017 12:56:00 GMT

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.

Re: SQL to SAS Date Formatting Character to Numeric

LinusH — Wed, 24 May 2017 13:08:15 GMT

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.