BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
yangtaotai
Calcite | Level 5

Hi everyone,

I wonder if there is a way I can run SQL code directly in SAS. I know I can run something like this below:

PROC SQL; 

   CONNECT TO ODBC as con

    (DATASRC="ABCREPORTING: Prod_Report" authdomain=SQLGRP_Temp_Reader_Auth);

   CREATE TABLE ABC_Table AS

   SELECT *

      FROM CONNECTION TO con2

(

  SELECT

  CASE BiasVa

  WHEN 1 THEN 'OO

  WHEN 2 THEN 'PP'

  END AS ABC_Type,

  UnitId,

  Stbi AS ABC_EWMA,

  AverageLightValue AS Avg_Actual,

  CONVERT(datetime, StartDateTime) AS StartDateTime,

  CONVERT(datetime, EndDateTime) AS EndDateTime

  FROM         Derating.UnitStbiParameter

  WHERE UnitID IN (1,2,3,4,5,6)

  AND BiasCodeId IN (1,2)

  AND ((EndDateTime > StartDateTime) OR EndDateTime IS NULL )

  GROUP BY UnitId, ABC, AverageLightValue, StartDateTime, EndDateTime

  Order By StartDateTime Desc, Unitid Asc

);

However, I feel like you cannot run something with special key words in SAS like below(the declare or @sign etc may not be executed properly)


DECLARE @InvestigationId VARCHAR(5) = '1166

IF object_id('tempdb..#ScanHistory') IS NOT NULL

  DROP TABLE #ScanHistory

SELECT ROW_NUMBER() OVER(ORDER BY IM.SubID, MSH.CreatedTime) AS RowID

,IM.InvestigationID

  ,IM.SubID

  ,IM.SampleDescription

  ,MS.Name [ModuleSubgroupName]

  ,CASE TE.TestFacilityID WHEN 2 THEN 'OTS' WHEN 10 THEN 'ATS' WHEN 11 THEN 'FTS' ELSE NULL END [ParentSiteID] 

  ,RIGHT(TE.EquipmentCode,4) [ArrayID]

  ,CASE WHEN ID.InverterModel != 'AUT 5.8' THEN 1 ELSE

CASE WHEN CAST(TES.SName AS INT) > 10 THEN 2 ELSE 1 END

END [MPPT]

,ID.InverterModel

  ,TE.EquipmentCode

  ,TE.MaxSlots

  ,TES.SName

  ,MSH.ReasonCode

  INTO #ScanHistory

  FROM ReliabilityDB_Report.dbo.RT_InvestigationModules IM WITH (NOLOCK)

inner join .................................................................

Just wonder if there is a way you can run SQL code directly without modifying it; Also typically when you have SQL code in hand but need to run the same thing in SAS, what is the best practice to do?

Thank you,

Tao

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

That is not SQL, it is PL/SQL which is more advanced than base SQL. To my knowledge, no you cannot run PL/SQL through a proc SQL passthrough.  However why do you need the other parts, the actual operational part is the select downwards, i.e. why would you need to check if there is a temporary file on the database, this is passthrough, the results of the query get returned to SAS not put into a temporary table so you don't need that part anyway.

Again, same with the declare, its not needed, all you are trying to do is get the results of a query on the database passed back to SAS for further processing.

A further thing whilst looking at your code, you may also need to tweek certain parts, the INTO #Scanhistory, wouldn't make much sense in this context, as you want the results back to SAS.

View solution in original post

4 REPLIES 4
jakarman
Barite | Level 11

Use real explicit pass thru that is; SAS/ACCESS(R) 9.4 for Relational Databases: Reference, Sixth Edition mark the execute block that is the one that says SAS do not interfere that, just pass those.  

---->-- ja karman --<-----
RW9
Diamond | Level 26 RW9
Diamond | Level 26

That is not SQL, it is PL/SQL which is more advanced than base SQL. To my knowledge, no you cannot run PL/SQL through a proc SQL passthrough.  However why do you need the other parts, the actual operational part is the select downwards, i.e. why would you need to check if there is a temporary file on the database, this is passthrough, the results of the query get returned to SAS not put into a temporary table so you don't need that part anyway.

Again, same with the declare, its not needed, all you are trying to do is get the results of a query on the database passed back to SAS for further processing.

A further thing whilst looking at your code, you may also need to tweek certain parts, the INTO #Scanhistory, wouldn't make much sense in this context, as you want the results back to SAS.

Reeza
Super User

RW9 wrote:

That is not SQL, it is PL/SQL which is more advanced than base SQL. To my knowledge, no you cannot run PL/SQL through a proc SQL passthrough. 

I thought you could run whatever SQL the database runs. So if you're on Oracle you should be fine?

RW9
Diamond | Level 26 RW9
Diamond | Level 26

I am pretty sure using the syntax select * from connection to xyz () will not be able to submit that type of code as its a query expecting a dataset back.  It may be that certain PL/SQL elements can be passed through by the use of execute() in a proc sql, per this post:

Still begs the question why though?

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
  • 4 replies
  • 956 views
  • 6 likes
  • 4 in conversation