I have some SQL code from Microsoft SQL server but not sure if you can directly run it in proc SQL?

[yangtaotai]

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

[jakarman]

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.  

[RW9 · Accepted solution]

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]

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]

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?