Macro string in Stored Procedure

Reply
Super Contributor
Posts: 418

Macro string in Stored Procedure

Hello all. I am attempting to run a macro string for asofdate into a sql server stored procedure.

Basically I have the macro varaible asofdate defined below.
%let asofdate=01/01/1981;

The Sql stored procedure needs this date to be enclosed in ' ' single quotes. However You cannot get a macro variable to enclose in single quotes in sas. Because of this, I tired using

%let new=%str(%')&asofdate.%str(%');  and %let new=%bquote('&asofdate'); and then calling &new within the pass through, however neither of these options worked.

In addition, instead of defining a new macro named new, and the calling it, I tried to use thes within the passthrough. Can anyone point out to me what I am doing incorrectly? I have tried searching online through a bunch of documentation yet nothing seems to work.

The code given below WORKS when run,

 

PROC SQL;

CONNECT TO ODBC (DSN=MYODBC);

EXECUTE (EXECUTE dbo.USP_DBMR_BuildReportTables 'C','01/01/1990',111,0,4,'1/1/2001' ,'12/31/2008') BY ODBC;

DISCONNECT FROM ODBC;

QUIT;

RUN;

However the following does not

PROC SQL;

CONNECT TO ODBC (DSN=MYODBC);

EXECUTE (EXECUTE dbo.USP_DBMR_BuildReportTables 'C',&hey.,111,0,4,'1/1/2001' ,'12/31/2008') BY ODBC;

DISCONNECT FROM ODBC;

QUIT;

RUN;

nor does the following.

PROC SQL;

CONNECT TO ODBC (DSN=MYODBC);

EXECUTE (EXECUTE dbo.USP_DBMR_BuildReportTables 'C',%str(%')&ASOFDATE.%str(%'),111,0,4,'1/1/2001' ,'12/31/2008') BY ODBC;

DISCONNECT FROM ODBC;

QUIT;

RUN;

Thanks for the help!

Super User
Posts: 5,082

Re: Macro string in Stored Procedure

Try it this way:

%let new = %unquote(%str(%'&asofdate%'));

SQL often has this problem with quoted quotes.

Good luck.

Super Contributor
Posts: 418

Re: Macro string in Stored Procedure

Thanks. I actually tried this method as well, to no avail.

I am beginning to question if the stored procedure might have an internal error and not the pass through function, I am going to have the person who wrote the procedure double check to make sure we  can zone in on the problem.

Brandon

Ask a Question
Discussion stats
  • 2 replies
  • 341 views
  • 0 likes
  • 2 in conversation