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?
You can do it directly in SQL. Post what you have now?
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.
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.
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.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.