Using Like operator as macro value in proc sql

[CP2]

I am having difficulty assigning an entire SQL statement as a macro variable using the like operator and WITH cte statement. The assigned macro variable will eventually be used in a proc SQL using the pass through facility. I am doing this because only a small portion of the SQL program will be changed depending on the diagnosis we are looking at.  Generally, I am using call symput and it has worked for other long sql strings but not this one. I think it is due to the  ;WITH cte statement either not being recognized in the advanced editor so assigns an error to it (when ;WITH is hard coded in the SQL program it works but 'WITH' is highlighted in red). 

In terms of the LIKE operator, I've tried concatenating the %, double %s and it doesn't seem to resolve them. I tried using contains instead of like but the pass through facility doesn't like it (ERROR: CLI execute error: [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'contains'.)

Is there anything else I can try? 

data NULL ;
%global drugstring ;
call symput('drugstring', ";WITH drugs AS ( SELECT DISTINCT ID FROM #Table WHERE dName like '%trastuzumab%' OR dName like '%pertuzumab%' OR dName like '%lapatinib%' )" ) ;
run;

%put &drugstring. ;

ERROR:

5700 data NULL ;
5701 %global drugstring ;
5702 call symput('drugstring', ";WITH drugs AS ( SELECT DISTINCT ID FROM #Table WHERE dName
5702! like '%trastuzumab%' OR dName like '%pertuzumab%' OR dName like '%lapatinib%' )" ) ;
WARNING: Apparent invocation of macro TRASTUZUMAB not resolved.
WARNING: Apparent invocation of macro PERTUZUMAB not resolved.
WARNING: Apparent invocation of macro LAPATINIB not resolved.
5703 run;

NOTE: The data set WORK.NULL has 1 observations and 0 variables.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

5704
5705 %put &drugstring. ;

NOTE: Line generated by the macro variable "DRUGSTRING".
1 ;WITH drugs AS ( SELECT DISTINCT ID FROM #Table WHERE dName like '%trastuzumab%' OR
----
180
1 ! dName like '%pertuzumab%' OR dName like '%lapatinib%' )
ERROR 180-322: Statement is not valid or it is used out of proper order.

[PaigeMiller]

In PROC SQL:

like %nrstr(%')%nrstr(%%)trastuzumab%nrstr(%%)%nrstr(%')

[CP2]

Thank you. This worked too!

[Tom · Accepted solution]

Why do you want your macro variable to start with ';WITH'?

I think that your basic trouble is just that the complexity of the string is confusing both the data step that you use to generate the macro variable and the %PUT statement you are using to check its value.

For the data step switch from using double quotes to single quotes to prevent SAS from trying the evaluate what looks like a macro call.  You can split the string into smaller pieces to make it easier if you want.  You should probably split it into small pieces anyway to make it easier to read the code.

call symputx('drugstring'
, ';WITH drugs AS ( SELECT DISTINCT ID FROM #Table'
||' WHERE dName like ''%trastuzumab%'''
||' OR dName like ''%pertuzumab%'''
||' OR dName like ''%lapatinib%'' )'
,'G'
) ;

To let you look at the value without causing the semi-colon to end te %PUT statement use the %SUPERQ() macro function.

%put %superq(drugstring);

[CP2]

Thank you. I need ;WITH because that code is used in a 1200+ line SQL program. Rather than have a separate SQL program for each diagnosis I thought it was easier to create macro strings and place them appropriately. This variable string will be blank for other diagnoses so will not be used at all in the proc SQL.

Your code is what I was looking for, especially for understanding why my put statement wasn't working. I also was not using double single quotes previously when I tried the concatenate.

Thanks!

[CP2]

Is it necessary to use 'G' in the symputx statement if %global was used at the beginning? Or is it not necessary to use %global when using 'G' ?

[Tom]

In general yes.  If you do not specify the optional second parameter to CALL SYMPUTX() then it will just behave like CALL SYMPUT() and replace the value of any existing macro variable of that name that it sees or create a new one in the current scope.  So if you have previously defined it as global and not hidden it by also creating a local macro variable with the same name then the global macro variable will be updated if it exists.

Note that SAS will process the macro code before running the generated SAS code.  So any %GLOBAL statement inside of a data step is actually executed before the data step.  So if you do use the %GLOBAL statement you should move it before the DATA _NULL_ statement to avoid confusing yourself. 

CALL SYMPUTX() can get tricky though.  If your data step is running inside of a macro you can actually use CALL SYMPUTX() to by-pass a local macro variable of the same name and update or create a global macro variable.  If you tried to use %GLOBAL to create a global macro variable of the same name as an already existing local macro variable you would get an error. Plus you could not read from the global macro variable. And the only way to write to it would be by using CALL SYMPUTX() anyway.