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

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

yangtaotai — Fri, 10 Apr 2015 13:29:09 GMT

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

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

jakarman — Fri, 10 Apr 2015 13:42:15 GMT

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.

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

RW9 — Fri, 10 Apr 2015 13:47:52 GMT

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.

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

Reeza — Fri, 10 Apr 2015 14:28:17 GMT

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?

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

RW9 — Fri, 10 Apr 2015 16:39:14 GMT

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?