BookmarkSubscribeRSS Feed
Ashpak
Calcite | Level 5

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.

5 REPLIES 5
SASKiwi
PROC Star

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
Calcite | Level 5

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
PROC Star

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
Calcite | Level 5

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
PROC Star

@Ashpak  - see my response to your new post.

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

Mastering the WHERE Clause in PROC SQL

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.

Discussion stats
  • 5 replies
  • 1368 views
  • 0 likes
  • 2 in conversation