DATA Step, Macro, Functions and more

SQL to SAS Date Formatting Character to Numeric

Reply
Occasional Contributor
Posts: 19

SQL to SAS Date Formatting Character to Numeric

[ Edited ]

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?  

PROC Star
Posts: 740

Re: SQL to SAS Date Formatting Character to Numeric

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

Occasional Contributor
Posts: 19

Re: SQL to SAS Date Formatting Character to Numeric

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.

Super User
Posts: 5,427

Re: SQL to SAS Date Formatting Character to Numeric

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.

Data never sleeps
Ask a Question
Discussion stats
  • 3 replies
  • 111 views
  • 0 likes
  • 3 in conversation