topic Question on SQL to SAS in SAS Procedures

Question on SQL to SAS

vomer — Thu, 09 Feb 2012 15:33:20 GMT

Hi Guys,

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?

Thanks.

Question on SQL to SAS

art297 — Thu, 09 Feb 2012 15:36:54 GMT

It would help if you post the query you were sent.

Question on SQL to SAS

vomer — Thu, 09 Feb 2012 15:47:34 GMT

It is a really long query but here is a snippet:

select p.last_name "Last Name",

p.given_name "First Name",

p.identifier1 "Card",

r.id "RID",

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",

Re: Question on SQL to SAS

art297 — Thu, 09 Feb 2012 15:54:03 GMT

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.;

proc sql;

your_entire_querry_ending_with_a_semi_colon

quit;

SAS doesn't have a to_char function but, if the querry is designed as a passthru to Oracle, it will work anyhow.

Question on SQL to SAS

Tom — Thu, 09 Feb 2012 16:00:31 GMT

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.

TO_CHAR is an Oracle function to convert a date to character string. In SAS you would use the PUT() function.
Your column names have spaces in them. You could set VALIDVARNAME=ANY and then convert the quote columns to SAS name litterals. So "R Date" would be converted to "R Date"n.

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 ****

);

QUIT;

Question on SQL to SAS

art297 — Thu, 09 Feb 2012 16:05:14 GMT

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.

Question on SQL to SAS

FriedEgg — Thu, 09 Feb 2012 18:59:04 GMT

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.

Re: Question on SQL to SAS

thoward — Fri, 07 Mar 2014 01:58:00 GMT

I have a question to you

Could I use a dot sql file to sas then to excel? I think you can but not sure

Re: Question on SQL to SAS

Peter_C — Fri, 07 Mar 2014 22:20:14 GMT

Should open a new thread for a new question.

however i can assure you that what you describe won't work.