topic Re: Why does this SQL code not work properly? in SAS Programming

Why does this SQL code not work properly?

PaigeMiller — Wed, 23 Jul 2014 19:05:27 GMT

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

Re: Why does this SQL code not work properly?

Haikuo — Wed, 23 Jul 2014 19:27:51 GMT

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

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

Haikuo

Re: Why does this SQL code not work properly?

PaigeMiller — Wed, 23 Jul 2014 19:39:13 GMT

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

Thanks!

Re: Why does this SQL code not work properly?

Tom — Thu, 24 Jul 2014 00:21:33 GMT

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-

Re: Why does this SQL code not work properly?

PaigeMiller — Thu, 24 Jul 2014 12:22:43 GMT

Thanks, Tom, I have made those changes.