Insert using SQL Pass Through

[prad001]

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

[Kurt_Bremser · Accepted solution]

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

[Tom]

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

execute by ODBC 
(
ABOVE SQL CODE
);

[prad001]

Thank you Kurt and Tom.

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

Thank you both again.