Help using Base SAS procedures

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 19
Accepted Solution

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

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


Accepted Solutions
Solution
‎04-10-2015 09:47 AM
Super User
Super User
Posts: 7,401

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

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


All Replies
Valued Guide
Posts: 3,208

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

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 --<-----
Solution
‎04-10-2015 09:47 AM
Super User
Super User
Posts: 7,401

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

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.

Super User
Posts: 17,815

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

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?

Super User
Super User
Posts: 7,401

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

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?

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 340 views
  • 6 likes
  • 4 in conversation