I have following set of statements in sql that i would like to run through SAS odbc connection in SQL server and create a report on data completeness of the tables present at our end /**************************************************************************************/ SET NOCOUNT ON DECLARE @Schema NVARCHAR(100) = 'EMR' DECLARE @Table NVARCHAR(100) = 'eMR_Assessment' DECLARE @SQL NVARCHAR(MAX) ='' IF OBJECT_ID ('EMR.eMR_Assessment_dchek') IS NOT NULL DROP TABLE EMR.eMR_Assessment_dchek CREATE TABLE EMR.eMR_Assessment_dchek (TableName sysname, ColumnName sysname , ColumnPosition int ,NullCount int , NonNullCount int, TotalCount int, NotNullpercentage decimal(20,2)) SELECT @SQL += 'SELECT '''+TABLE_NAME+''' AS TableName , '''+COLUMN_NAME+''' AS ColumnName, '''+CONVERT(VARCHAR(5),ORDINAL_POSITION)+''' AS ColumnPosition, SUM(CASE WHEN '+COLUMN_NAME+' IS NULL THEN 1 ELSE 0 END) CountNulls , COUNT(' +COLUMN_NAME+') CountnonNulls, count(*) TotalCount, (cast(COUNT(' +COLUMN_NAME+') as decimal (20,2))/ cast(count(*) as decimal (20,2)))*100 NotNullpercentage FROM '+QUOTENAME(TABLE_SCHEMA)+'.'+QUOTENAME(TABLE_NAME)+';'+ CHAR(10) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @Schema AND TABLE_NAME = @table INSERT INTO EMR.eMR_Assessment_dchek EXEC sp_executesql @SQL SELECT * FROM EMR.eMR_Assessment_dchek /***************************************************************************************/ I have incorporated above query in to sas execute statements /********************************************************************************/ proc sql; connect to odbc(dsn=ambo); execute (SET ANSI_NULLS ON)by odbc; execute (SET QUOTED_IDENTIFIER ON)by odbc; execute (SET ANSI_PADDING ON)by odbc; execute ( SET NOCOUNT ON DECLARE @Schema NVARCHAR(100) = 'EMR' DECLARE @Table NVARCHAR(100) = 'eMR_Assessment' DECLARE @SQL NVARCHAR(MAX) ='' IF OBJECT_ID ('EMR.eMR_Assessment_dchek') IS NOT NULL DROP TABLE EMR.eMR_Assessment_dchek )by odbc; execute ( CREATE TABLE EMR.eMR_Assessment_dchek (TableName sysname, ColumnName sysname , ColumnPosition int ,NullCount int , NonNullCount int, TotalCount int, NotNullpercentage decimal(20,2)) )by odbc; execute ( SELECT @SQL += 'SELECT '''+TABLE_NAME+''' AS TableName , '''+COLUMN_NAME+''' AS ColumnName, '''+CONVERT(VARCHAR(5),ORDINAL_POSITION)+''' AS ColumnPosition, SUM(CASE WHEN '+COLUMN_NAME+' IS NULL THEN 1 ELSE 0 END) CountNulls , COUNT(' +COLUMN_NAME+') CountnonNulls, count(*) TotalCount, (cast(COUNT(' +COLUMN_NAME+') as decimal (20,2))/ cast(count(*) as decimal (20,2)))*100 NotNullpercentage FROM '+QUOTENAME(TABLE_SCHEMA)+'.'+QUOTENAME(TABLE_NAME)+';'+ CHAR(10) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @Schema AND TABLE_NAME = @table )by odbc; execute (SET ANSI_PADDING OFF)by odbc; quit; execute ( INSERT INTO EMR.eMR_Assessment_dchek EXEC sp_executesql @SQL )by odbc; execute ( SELECT * FROM EMR.eMR_Assessment_dchek )by odbc; quit; /*********************************************************************************************/ But I am getting following error in SAS EG: ERROR: CLI execute error: [Microsoft][ODBC SQL Server Driver][SQL Server]Must declare the scalar variable "@sql". NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements. execute (SET ANSI_PADDING OFF)by odbc; NOTE: Statement not executed due to NOEXEC option. quit; NOTE: The SAS System stopped processing this step because of errors. Whats possibly going wrong!
... View more
