02-09-2012 10:33 AM
I was recently sent an SQL query and asked to run it in SAS. I am unsure on how to do this?
I know there is a proc SQL command but I have never used it before...
Is there any automated conversion?
02-09-2012 10:47 AM
It is a really long query but here is a snippet:
select p.last_name "Last Name",
p.given_name "First Name",
TO_CHAR(r.referral_date, 'yyyy/mm/dd hh:mi:ss') "R Date",
ROUND(pt.unstable_interval / 86400000,1) "D Interval",
t1.name "Choice 1",
t2.name "Choice 2",
t3.name "Choice 3",
02-09-2012 10:54 AM
Depending upon how the rest of the query is written, it may run in SAS by just wrapping it within a proc sql statement. e.g.;
SAS doesn't have a to_char function but, if the querry is designed as a passthru to Oracle, it will work anyhow.
02-09-2012 11:00 AM
Basically you wrap your query with PROC SQL and QUIT statements.
If you want to save the results you probably also need to add a CREATE TABLE xxxx or CREATE VIEW xxxx clause before the SELECT statement. If you want to make a report then your probably do not want to use SQL code at all and instead want to look at PROC PRINT or PROC REPORT as you will be able to have much more control over the report.
If you want to run it totally in SAS then you will also need to convert the Oracle syntax to SAS syntax.
If you data is actually still in Oracle then you might want to use pass through SQL . To do that your query is structured like this:
PROC SQL ;
CONNECT TO ORACLE (.....);
select * from connection to oracle (
**** put your Oracle SQL code here ****
02-09-2012 11:05 AM
Tom, I could be wrong, but I think that with the secondary names enclosed in quotation marks the new name just becomes a variable label, thus setting validvarname=any wouldn't be necessary.
02-09-2012 01:59 PM
Ignoring the other comments Tom made, which are valid and only replying here to the idea of using validvarname option:
When using SQL Pass-through as in Tom's post:
Using validvarname is not necessary when using just a select statement, like Tom said.
Using validvarname is also not necessary when using a create table however, if you do not mind in your case the spaces being converted to underscores. I.e. "R Date" in Oracle would be "R_Date" in SAS with validvarname=V7 (default).
Using validvarname=any when using a create table will produce the identical expected variable names "R Date"n in SAS.
When not using Pass-through as in Art's post:
In SAS this syntax would assign the quoted string as a lable and keep the variable name as it came from. Just like Art said. If you wanted to assign the labels as variable names instead you would need to use the as keyword and "VAR NAME"n to define the column name, using validvarname=any would also be required here.