I am trying to use SQL to create a text string inside a macro variable ... actually the text string should be a valid SAS data step statement, as you will see. The problem appears to be a missing semi-colon in the resulting macro variable, as illustrated in the output of the 2nd %PUT statement, as shown below. Yet clearly, the string I am trying to create, using the CATS function in PROC SQL should end with a semi-colon. So what happens to this semi-colon? Why doesn't the results of the %PUT statement contain a semi-colon?
Here is the code, and then below are the results of the two %PUT statements.
data use;
input lots $ wafers $24.;
cards;
AA001 ALL
AA002 ALL
AA003 3,7,12,15
AA004 ALL
AA007
AA005 1,2,3,4,8,9
AA006 all
AA008 ALL
;
run;
proc sql noprint;
select count(distinct lots) into :waferdelcount from use where upcase(wafers)^='ALL' and not missing(wafers);
select cats("if upcase(lot)=""",upcase(lots),""" and wafer in (",wafers,") then delete;") into :wafertext1-:wafertext%left(&waferdelcount)
from use where upcase(wafers)^='ALL' and not missing(wafers);
quit;
%put WAFERDELCOUNT &waferdelcount;
%put WAFERTEXT1 **** &wafertext1 ****;
The results of the two %PUT statements are shown next, there is no semi-colon as the last character of the output of the 2nd %PUT statement. Why not?
311 %put WAFERDELCOUNT &waferdelcount;
WAFERDELCOUNT 2
312 %put WAFERTEXT1 **** &wafertext1 ****;
WAFERTEXT1 **** if upcase(lot)="AA003" and wafer in (3,7,12,15) then delete
semi colon is there, your macro variable is fine, you just need to mask it when it is resolved:
%put WAFERTEXT1 **** %bquote(&wafertext1) **** ;
Haikuo
semi colon is there, your macro variable is fine, you just need to mask it when it is resolved:
%put WAFERTEXT1 **** %bquote(&wafertext1) **** ;
Haikuo
Oh ... so now I know I wasted all that time thinking it was an SQL problem, when it was just a MACRO issue ...
Thanks!
Note that you do NOT need to run the query twice to get the number of items returned. SAS will do that automatically. You can set the upperbound to a very large number. SAS will only create the ones that it needs.
proc sql noprint;
select cats("if upcase(lot)="""
,upcase(lots)
,""" and wafer in (",wafers,") then delete;"
)
into :wafertext1-:wafertext999999
from use
where upcase(wafers)^='ALL' and not missing(wafers)
;
%let WAFERDELCOUNT=&sqlobs ;
quit;
%put WAFERDELCOUNT = &waferdelcount;
%put WAFERTEXT1 = |%superq(wafertext1)|;
And if you have a newish version of SAS you can just use:
into :wafertext1-
Thanks, Tom, I have made those changes.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.