DATA Step, Macro, Functions and more

Why does this SQL code not work properly?

Accepted Solution Solved
Reply
Trusted Advisor
Posts: 1,926
Accepted Solution

Why does this SQL code not work properly?

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




Accepted Solutions
Solution
‎07-23-2014 03:27 PM
Respected Advisor
Posts: 3,156

Re: Why does this SQL code not work properly?

Posted in reply to PaigeMiller

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

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

Haikuo

View solution in original post


All Replies
Solution
‎07-23-2014 03:27 PM
Respected Advisor
Posts: 3,156

Re: Why does this SQL code not work properly?

Posted in reply to PaigeMiller

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

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

Haikuo

Trusted Advisor
Posts: 1,926

Re: Why does this SQL code not work properly?

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

Thanks!

Super User
Super User
Posts: 7,055

Re: Why does this SQL code not work properly?

Posted in reply to PaigeMiller

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-


Trusted Advisor
Posts: 1,926

Re: Why does this SQL code not work properly?

Thanks, Tom, I have made those changes.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 216 views
  • 3 likes
  • 3 in conversation