topic Re: Insert using SQL Pass Through in SAS Data Management

Insert using SQL Pass Through

prad001 — Tue, 08 Mar 2022 15:38:00 GMT

Hi All,

I am trying to insert rows in SQL Server DB using SAS SQL Pass through facility.

The below code works fine in SQL but I am not sure what I am missing while I am incorporating it in SAS. Could you please tell me the appropriate changes to be made in SAS.

USE [SDMS]
GO


INSERT INTO [sur].[FactSurveyTotal]
([SurveySK]
,[TotalTypeNm]
,[TotalSubTypeNm]
,[DOSTotalCnt]
,[EDWTotalCnt]
,[WithNAPct]
,[WithoutNAPct]
)
VALUES (
(SELECT SurveySK FROM sur.DimSurvey where SurveyYearNum='2020' AND SurveyTypeNm='Physician')
,'Test Type'
, 'Test SubType'
,1
,2
,3
,4
)

With

proc sql;
connect to odbc(dsn="XXXXXX" user=XXXX password="XXXXX" );
(
ABOVE SQL CODE
);
QUIT;

Am I missing anything or should I modify the sql code in any way ?

Thank you,

Prad

Re: Insert using SQL Pass Through

Kurt_Bremser — Tue, 08 Mar 2022 15:43:49 GMT

For a pure pass-through command, you need the SAS SQL EXECUTE statement.

Re: Insert using SQL Pass Through

Tom — Tue, 08 Mar 2022 16:55:31 GMT

You need to tell SAS that you want to run the command on the remote.

execute by ODBC 
(
ABOVE SQL CODE
);

Re: Insert using SQL Pass Through

prad001 — Tue, 08 Mar 2022 16:58:05 GMT

Thank you Kurt and Tom.

"Execute" is what I missed. It works fine now.

Thank you both again.