Dynamic SQL code storing in macro variable and call to Execute proc sql;

[Ashpak]

Hi I have Dynamic SQL code written in PROC sql procedure , I am forming Dynamic sql and storing  sQL code into variable and calling that variable

%let Effdt = '';

%let Query = '';

Proc SQL;

connect to odbc(DSN=&DSN user=&user password=&pwds);

select * from connection to odbc

(

SELECT @Effdt += + QUOTENAME(EFF_DT)+ ','

FROM

(

select distinct EFF_DT from

(

select ROW_NUMBER() over (order by EFF_DT desc )as rownum,a.EFF_DT

from (

select distinct case when qrm_db='Dec20' then '2020-12-31'

when qrm_db='Jan21' then '2021-01-31'

when qrm_db='Nov20' then '2020-11-30'

when qrm_db='Oct20' then '2020-10-31'

else '2999-01-01' end as EFF_DT

from [FASTControl].[dbo].ALM_NVPData where QRM_DB in ('Jan21','Dec20','Nov20','Oct20')

)a

)F

where f.rownum<=4

) eff_dt order by EFF_DT

SET @Effdt = LEFT(@Effdt, LEN(@Effdt)-1)

Set @Query='

select * from

(

select case when qrm_db=''Dec20'' then ''2020-12-31''

when qrm_db=''Jan21'' then ''2021-01-31''

when qrm_db=''Nov20'' then ''2020-11-30''

when qrm_db=''Oct20'' then ''2020-10-31''

else ''2999-01-01'' end EFF_DT , NVPnode,RateIndex from [FASTControl].[dbo].ALM_NVPData

where QRM_DB in (''Jan21'',''Dec20'',''Nov20'',''Oct20'')

)a

pivot

( max(RateIndex)

For EFF_DT in ('+@Effdt+')

)pivts'

;

EXECUTE sp_executesql @Query;

run;

I am able to execute above SQL code in SQL server but from SAS getting below error message may be variable not able to store dynamic SQL code into memory. do we know other solution to this ?

NOTE: The quoted string currently being processed has become more than 262 characters long. You might have unbalanced quotation

marks.

[SASKiwi]

You will have to do this in an EXECUTE statement, not a SELECT statement:

Proc SQL;

connect to odbc(DSN=&DSN user=&user password=&pwds);

execute(

SELECT @Effdt += + QUOTENAME(EFF_DT)+ ','

FROM

(

select distinct EFF_DT from

 

(

select ROW_NUMBER() over (order by EFF_DT desc )as rownum,a.EFF_DT

from (

select distinct case when qrm_db='Dec20' then '2020-12-31'

when qrm_db='Jan21' then '2021-01-31'

when qrm_db='Nov20' then '2020-11-30'

when qrm_db='Oct20' then '2020-10-31'

else '2999-01-01' end as EFF_DT

from [FASTControl].[dbo].ALM_NVPData where QRM_DB in ('Jan21','Dec20','Nov20','Oct20')

 

)a

)F

where f.rownum<=4

) eff_dt order by EFF_DT

SET @Effdt = LEFT(@Effdt, LEN(@Effdt)-1)

Set @Query='

select * from

(

select case when qrm_db=''Dec20'' then ''2020-12-31''

when qrm_db=''Jan21'' then ''2021-01-31''

when qrm_db=''Nov20'' then ''2020-11-30''

when qrm_db=''Oct20'' then ''2020-10-31''

else ''2999-01-01'' end EFF_DT , NVPnode,RateIndex from [FASTControl].[dbo].ALM_NVPData

where QRM_DB in (''Jan21'',''Dec20'',''Nov20'',''Oct20'')

)a

pivot

( max(RateIndex)

For EFF_DT in ('+@Effdt+')

)pivts'

;

EXECUTE sp_executesql @Query;
) by ODBC;
quit;

[Ashpak]

Thanks, but getting below error message

ERROR: CLI execute error: [OpenLink][ODBC][SQL Server]Incorrect syntax near the keyword 'in'. (156) : [OpenLink][ODBC][SQL

Server]General SQL Server error: Check messages from the SQL Server (156)

NOTE

for the below code

Proc SQL;

connect to odbc(DSN=&DSN user=&user password=&pwds);

execute

(

DECLARE @Effdt NVARCHAR(MAX) = '';

DECLARE @Query NVARCHAR(MAX) = '';

SELECT @Effdt += + QUOTENAME(EFF_DT)+ ','

FROM

(

select distinct EFF_DT from

(

select ROW_NUMBER() over (order by EFF_DT desc )as rownum,a.EFF_DT

from (

select distinct case when qrm_db='Dec20' then '2020-12-31'

when qrm_db='Jan21' then '2021-01-31'

when qrm_db='Nov20' then '2020-11-30'

when qrm_db='Oct20' then '2020-10-31'

else '2999-01-01' end as EFF_DT from [FASTControl].[dbo].ALM_NVPData where QRM_DB in ('Jan21','Dec20','Nov20','Oct20')

)a

)F

where f.rownum<=4

) eff_dt order by EFF_DT

SET @Effdt = LEFT(@Effdt, LEN(@Effdt)-1)

Set @Query='

select * from

(

select case when qrm_db=''Dec20'' then ''2020-12-31''

when qrm_db=''Jan21'' then ''2021-01-31''

when qrm_db=''Nov20'' then ''2020-11-30''

when qrm_db=''Oct20'' then ''2020-10-31''

else ''2999-01-01'' end EFF_DT , NVPnode,RateIndex from [FASTControl].[dbo].ALM_NVPData

where QRM_DB in (''Jan21'',''Dec20'',''Nov20'',''Oct20'')

)a

pivot

( max(RateIndex)

For EFF_DT in ('+@Effdt+')

)pivts'

;

EXECUTE sp_executesql @Query;

) by odbc;

quit;

[SASKiwi]

You've got an SQL Server SQL syntax error in there somewhere but I don't know which "IN" is causing the problem. Obviously I can't test that for you. Try simplifying your SQL, chopping out sections, until you have found the problem.  

Edit: I know our SQL Server doesn't like double quotes. Try converting these to single quotes.

[Ashpak]

I tried with Simple code

Proc SQL;

connect to odbc(DSN=&DSN user=&user password=&pwds);

Execute ("DECLARE @Query NVARCHAR(MAX) = '';

Set @Query='select distinct qrm_db from [FASTControl].[dbo].ALM_NVPData'

PRINT @Query

EXECUTE sp_executesql @Query") by odbc;

quit;

Log says

Proc SQL;

31 connect to odbc(DSN=&DSN user=&user password=&pwds);

32 Execute ("DECLARE @Query NVARCHAR(MAX) = '';

33 Set @Query='select distinct qrm_db from [FASTControl].[dbo].ALM_NVPData'

34 PRINT @Query

35 EXECUTE sp_executesql @Query") by odbc;

36 quit;

NOTE: PROCEDURE SQL used (Total process time):

real time 0.17 seconds

cpu time 0.04 seconds

I don't see any output is this SQL executes?

[SASKiwi]

@Ashpak  - see my response to your new post.