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
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.
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.
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.
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?
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?
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.