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.
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;
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;
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.
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?
@Ashpak - see my response to your new post.
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 16. Read more here about why you should contribute and what is in it for you!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.