BookmarkSubscribeRSS Feed
vomer
Obsidian | Level 7

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.

8 REPLIES 8
art297
Opal | Level 21

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

vomer
Obsidian | Level 7

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

art297
Opal | Level 21

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.

Tom
Super User Tom
Super User

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;

art297
Opal | Level 21

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.

FriedEgg
SAS Employee

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.

thoward
Calcite | Level 5

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

Peter_C
Rhodochrosite | Level 12

Should open a new thread for a new question.

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

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 8 replies
  • 3909 views
  • 2 likes
  • 6 in conversation