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
I'm new to SAS ,
Could you please help with me that how to use explicit SQL in SAS?
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?
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...
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;
@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.
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!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: