creating Dynamic SQL in SAS

[Ashpak]

Hi Team

I have Dynamic SQL with Pivot Functionality , could you please help how to create this PROC SQL

here is my code which runs in SQL server. Same SQL code I need to use in

PROC SQL procedure

DECLARE @Effdt NVARCHAR(MAX) = ''

DECLARE @Query NVARCHAR(MAX) = ''

　

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

FROM

(

SELECT DISTINCT eff_dt

FROM [reference].[AL_ALM_RATE_INDEX]

) AS eff_dt

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

--print @Effdt

Set @Query=' select * from

(

select data_point, EFF_DT,RATE_INDEX,RATE_INDEX_NAME from [reference].[AL_ALM_RATE_INDEX]

where len(RATE_INDEX_NAME)>0 and RATE_INDEX_NAME not like ''FTP_%''

)a

pivot

( max(RATE_INDEX)

for EFF_DT in ('+@Effdt+')

)pivts'

--PRINT @Query

EXECUTE sp_executesql @Query

[Reeza]

Can you use explicit SQL Pass through?

[Ashpak]

I'm new to SAS ,

Could you please help with me that how to use explicit SQL in SAS?

[ketpt42]

This documentation will help: SQL Pass-Through Facility Specifics for Microsoft SQL Server . However, without knowing your specific SAS environment, it's going to be difficult to assist you without more information from you. Are you running a local version of SAS, or in a server/grid environment? If a server environment, how is authentication to SQL Server handled in your environment? 

[Reeza]

You can use pass through, which lets you pass your SQL commands directly to the server. 

https://www.lexjansen.com/sesug/2017/DM-57.pdf

If you need to migrate this code to SAS, that's a different task and you'll likely want to switch this out of SQL and use either a data step or macro language.

UCLA introductory tutorial on macro variables and macros
https://stats.idre.ucla.edu/sas/seminars/sas-macros-introduction/


Examples of common macro usage
https://communities.sas.com/t5/SAS-Communities-Library/SAS-9-4-Macro-Language-Reference-Has-a-New-Ap...

[Ashpak]

thanks  tried this way giving error now

51 EXECUTE sp_executesql &Query

52 ) by odbc;

ERROR: CLI execute error: [Microsoft][SQL Server Native Client 11.0][SQL Server]'nvarchar' is not a recognized built-in function

name. : [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near '('. : [Microsoft][SQL Server Native

Client 11.0][SQL Server]Incorrect syntax near 'max'.

code

%let Effdt nvarchar(max)='';

%let Query nvarchar(max)='';

proc sql;

connect to odbc(DSN=CALM_HUB_PRD user="AD-ENT\aaaa" password=&pwds);

execute(

SELECT &Effdt += + QUOTENAME(eff_dt)+ ','

FROM

(

SELECT DISTINCT eff_dt

FROM CALM_HUB.reference.AL_ALM_RATE_INDEX

)

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

SET &Query=

'select * from

(

select data_point, EFF_DT,RATE_INDEX,RATE_INDEX_NAME from [reference].[AL_ALM_RATE_INDEX]

where len(RATE_INDEX_NAME)>0 and RATE_INDEX_NAME not like ''FTP_%''

)a

pivot

( max(RATE_INDEX)

for EFF_DT in ('+&Effdt+')

)pivts'

EXECUTE sp_executesql &Query

) by odbc;

　

[Reeza]

Well you didn't pass in any dates and macro variables only resolve in double quotes.

[Ashpak]

You mean we have to assign with double quotes macro variables . effective date is having dates converting into characters because I will be passing dates with [ ] in pivot sql function

This SQL perfectly working in sql server but not SAS

[Reeza]

Are you submitting the exact same code? I suspect not so compare your log to your submitted code to see how it differs.

[SASKiwi]

@Ashpak  - From what I see in your original query you don't need to define SAS macro variables as these are declared SQL variables and you need to add your SQL DECLARE statements back in. You should be able to simply copy and paste your original SQL Server code into the SAS EXECUTE statement. Maybe you could start with a simple test like this:

proc sql;
   connect to odbc (noprompt = "server=MySQLServer;DRIVER=SQL Server Native Client 11.0;Trusted_Connection=yes;"); 
   execute (delete from [MyDatabase].[MySchema].[MyTable]
           ) by ODBC;
   disconnect from ODBC;
quit;

Please note EXECUTE does not support reading a result set from the SQL processing. If your SQL code produces a result set then you will need to insert that into an SQL Server temporary table, then query that with a separate SELECT * from connection to ODBC statement.