BookmarkSubscribeRSS Feed
dsriggs
Fluorite | Level 6

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?  

3 REPLIES 3
PeterClemmensen
Tourmaline | Level 20

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

dsriggs
Fluorite | Level 6
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
Tourmaline | Level 20

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

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!

How to Concatenate Values

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.

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