Why does this SQL code not work properly?

[PaigeMiller]

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

[Haikuo · Accepted solution]

semi colon is there, your macro variable is fine, you just need to mask it when it is resolved:

%put WAFERTEXT1 **** %bquote(&wafertext1) **** ;

Haikuo

[PaigeMiller]

Oh ... so now I know I wasted all that time thinking it was an SQL problem, when it was just a MACRO issue ...

Thanks!

[Tom]

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-

[PaigeMiller]

Thanks, Tom, I have made those changes.