BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
PaigeMiller
Diamond | Level 26

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



--
Paige Miller
1 ACCEPTED SOLUTION

Accepted Solutions
Haikuo
Onyx | Level 15

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

4 REPLIES 4
Haikuo
Onyx | Level 15

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
Diamond | Level 26

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!

--
Paige Miller
Tom
Super User Tom
Super User

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
Diamond | Level 26

Thanks, Tom, I have made those changes.

--
Paige Miller

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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