How to break apart a parameter

[deleted_user]

I have a parameter in a stored process as follows:
ABC, DEF,GHI, JKL, MNO, PQR,STU

My question: How can I break this apart into groups of entered three character components, when sometimes not all seven components will be entered, and the users will sometimes place a comma and space between desired entries, and sometimes will only enter a comma? The reason I want to break up the parameter is so later on, in a PROC SQL statement, I can end up with something like 'IF FieldA IN ('ABC', 'DEF'.....etc). I need to be able to place quotes around each entry the user keys in.

Thanks in advance to those of you who reply.

[Cynthia_sas]

Hi:
Look at this previous post
http://support.sas.com/forums/thread.jspa?messageID=6236ᡜ

There is an example of breaking up a macro parameter into component parts using %SYSFUNC and the SCAN function.

If your input parameter will have commas in it, then you need to protect the comma from being interpreted as an argument to the SCAN function. By default both the space and the comma are included in the delimiters that the SCAN function would use to break up a text string into pieces:
[pre]
blank . < ( + & ! $ * ) ; ^ - / , % |
[/pre]

So in the case you describe, your SCAN function would not need any 3rd argument. But, the code in the prior post would have to change slightly to protect the string with commas:
[pre]
%let old_list = %str(a, b c, d) ;
[/pre]

And then you will get the same results as shown in the previous posting.

cynthia

[deleted_user]

Thank you! I would not have thought of using SCAN.... that's what I get for only working with SAS for a few months. I appreciate the information, and hope you had a nice Labor Day weekend.

[deleted_user]

Hi again, Cynthia,

Thanks for the previous help with this question. I now have another question related to the same thoughts. When I entered the parameter as follows: ABC, DEF, GHI and then execute my stored process, I got an error message about unbalanced quotes (the log showed "ABC, DEF, GHI"). So...I tried using the UNQUOTE function, which then gave me the following as separated parameters (unquote, ABC, DEF, GHI). What am I doing incorrectly? I don't want the word unquote to end up as one of my desired parameters. Also, how would I then concatenate the now separated parameters back together, with single quotes around each one? I know I'm just not seeing something, but can't figute it. Here's my code snippet:

%macro field_where;

%let campuscodesclass = unquote(&campuscodes);

/**********************************************************************************************
/*** Job Codes processing ***
/**********************************************************************************************
*/

%let jobcodescls = "%superq(jobcodes)";
%local i;
%if %superq(jobcodes0) ne
%then %do i=2 %to %superq(jobcodes0);
%let jobcodescls = &jobcodescls,"%superq(jobcodes&i)";
%end;

%mend field_where;

%field_where;
run;
/************************************
/* Calls to other macros
*/
%campusname;

%prevyr_curryr;

%enter_year;
/************************************
/* Break out the campus codes for upcoming proc sql
*/
data colleges;
%let campus001 = %sysfunc(scan(&campuscodesclass,1));
%let campuscodescls = &campus001;
%let campus002 = %sysfunc(scan(&campuscodesclass,2));
%let campuscodescls = &campuscodescls, &campus002;
%let campus003 = %sysfunc(scan(&campuscodesclass,3));
%let campuscodescls = &campuscodescls, &campus003;
%let campus004 = %sysfunc(scan(&campuscodesclass,4));
%let campuscodescls = &campuscodescls, &campus004;
%let campus005 = %sysfunc(scan(&campuscodesclass,5));
%let campuscodescls = &campuscodescls, &campus005;
%let campus006 = %sysfunc(scan(&campuscodesclass,6));
%let campuscodescls = &campuscodescls, &campus006;
%let campus007 = %sysfunc(scan(&campuscodesclass,7));
%let campuscodescls = &campuscodescls, &campus007;

run;

Thanks tremendously again.

[Cynthia_sas]

Hi:
I'm confused. At one point, if I remember correctly, you had designed a stored process that allowed the users to select MULTIPLE choices for a stored process parameter. So in that case, you would already have numbered parameters (&CHOICE1, &CHOICE2, &CHOICE3) that will have come back to your program from the server.

Have you designed a different stored process in which you allow the users to type in a text string? And now you want to break apart the "big" text string (which may contain commas or spaces), only to join the separate pieces back together again for a where clause? And the resulting where clause will be a macro variable?

Here are some previous forum posts that are still relevant:
http://support.sas.com/forums/thread.jspa?messageID=3088ఐ
http://support.sas.com/forums/thread.jspa?messageID=6195ᠳ
http://support.sas.com/forums/thread.jspa?messageID=6259ᡳ

And one thought...if you see the string "unquote" in your parsed macro variables, that may because you used UNQUOTE instead of the
%UNQUOTE function. I'm pretty sure that you don't want to "unprotect" the text string with the %UNQUOTE function...unless you know it's been protected with a different quoting function.

Once you move out of the world of multiple choices for input parameters (where the creation of the serially numbered parameters is managed by the stored process server) and into the world of parsing macro variables, which may contain "unsafe" characters (unsafe syntactically...because somebody could enter a comma or a single quote or a semi-colon that would "mess" up your SAS code in the compiler). Then you get into the world of Macro Facility QUOTING functions.

I like to think of these QUOTING functions as "masking" or "protection" functions because they mask the normal meaning of certain punctuation or words and prevent them from being mis-interpreted as part of a SAS program. For example, you might have the value "OR" meaning Oregon -- but in some instances, you need to protect the "OR" from being treated as a logical operator. Or, the text string "Sales > 500", you may need to protect the > from being treated as part of an HTML tag. Or the list "ABC, DEF" -- COULD look like an invalid argument to a function. Etc, etc.

A full discussion of MACRO quoting functions is way beyond the scope of what can be addressed in the forum. There is a whole section in the Macro documentation on Macro Quoting functions:
How Macro Quoting Works
Other Functions That Perform Macro Quoting
Introduction to Macro Quoting
Deciding How Much Text to Mask with a Macro Quoting Function
Deciding When to Use a Macro Quoting Function and Which Function to Use
Referring to Already Quoted Variables

You may need to "step up" your quoting protection by either using a different macro quoting function or by changing to the use of %SCAN or
%QSCAN functions (instead of %SYSFUNC and "regular" SCAN). This is complicated by the fact that in some instances, when you allow the users to type in a text string (as for an input parameter) -- that string may already be "protected" at the server before you get it and you may have to "unprotect" it in your stored process code by using the STPSRV_UNQUOTE2 function. (a stored process specific quoting function).

At this point, because you have quite a lengthy bunch of code and because more information is needed to help you debug the problem (how is the input parameter defined, what are the possible values, how is your program dealing with the input parameters, and what you want to do, ultimately, with your input parameters), your best bet for a quick resolution is to contact Tech Support.

cynthia

[deleted_user]

Hi, Cynthia,

You are no more confused than I am. Yes, I did and do have SP's that allow users to select MULTIPLE choices, but our Director and CIO now wants any new SP's to have a "big" text string of what used to be multiple choices. That's why I'm trying to figure out the ways of breaking up strings. And, yes, the resulting where cluase could be a macro variable or could be part of a data step.

Before I forget.... you were correct about having the word QUOTE instead of the word %QUOTE.

You also mentioned Macro Documentation.... where can I find that? Is it online, or is it in the SAS Books/Manuals?

Thanks once again!!!!

[Cynthia_sas]

In a break during class, so I'll be brief:
[pre]
From inside SAS Display Manager:
SAS Help and Documentation --> Contents tab
SAS Products -->
Base SAS -->
SAS Macro Reference

From online documentation site
http://support.sas.com/documentation/onlinedoc/sas9doc.html
If prompted to logon, use the same user id and password that you use to access the Forum
Then select SAS 9.1.3 link entitled "SAS OnlineDoc 9.1.3 for the Web"
Then select Contents -->
Expand Base SAS topic -->
Expand SAS Macro Language: Reference topic
[/pre]

cynthia

[deleted_user]

Thanks for the information.

By the way, I did manage to get my one line parameter entry to successfully break into smaller parts and then to recombine, with quotes around each entry for subsequent use in a Proc Sql bunch of code. Here is how I got it to work:

%let campuscodesclass = %unquote(&campuscodes);

%if %scan(%quote(&campuscodesclass),1) ne %then %do;
%let campus001 = %scan(%quote(&campuscodesclass),1);
%let campuscodescls = "&campus001";
%end;
%if %scan(%quote(&campuscodesclass),2) ne %then %do;
%let campus002 = %scan(%quote(&campuscodesclass),2);
%let campuscodescls = &campuscodescls, "&campus002";
%end;
%if %scan(%quote(&campuscodesclass),3) ne %then %do;
%let campus003 = %scan(%quote(&campuscodesclass),3);
%let campuscodescls = &campuscodescls, "&campus003";
%end;
%if %scan(%quote(&campuscodesclass),4) ne %then %do;
%let campus004 = %scan(%quote(&campuscodesclass),4);
%let campuscodescls = &campuscodescls, "&campus004";
%end;
%mend field_where;

Thank you so much for your help and assistance and knowledge. You are being a lifesaver and I appreciate it.

[Cynthia_sas]

Hi:
Glad you got it working!

Just a comment on this
"...but our Director and CIO now wants any new SP's to have a "big" text string of what used to be multiple choices."

I am not a fan of allowing end users to type in big text strings. And although you did come up with a working solution for this particular situation, you cannot predict or anticipate the types of text strings that COULD be typed which MIGHT cause problems in some syntax situations. For example, your program could get lost in "quote land" or in some other bad syntax place if the users typed in this text string -- maybe they just weren't paying attention and hit RUN too fast:
[pre]
abc', end#(;
[/pre]

Been there, done that, have worn out several T-shirts. I understand the temptation to allow them to type in a text string and then to make you do the work to break up the text string in the macro code. But if you're going to go forward this technique, then you probably need a LOT more validation and error checking before you pass these values to a WHERE clause.

cynthia

[deleted_user]

Amen to that!! Any time you allow a user to type in something, the chances of error entry becomes a tsunami. Like you, I've been there, done that in previous jobs. But, when the boss speaks..........

And yes, MUCH more validation will now be needed, which adds to code bloat. Ain't progress grand?? 🙂

Thanks again so much for your outstanding help. Rest assured I'll be asking more questions 😉

[LawrenceHW]

Just adding my two pence worth here.

I agree. Don't like users having long text strings as input. Leads to all sorts of horrors with %STR, %BQUOTE, %SUPERQ, etc. etc. etc.

I often find that using a DATA _NULL_ step reading the macro variable into a data step variable much easier to deal with as there are lots of character manipulation functions to play around with. Then if you want a list of values in a list which are quoted why not use the SQL code of "SEPERATED BY ' '"? Always a useful little function. Or you could use RETAINs within the DATA step and concatenation functions. Can be easier to de-bug and maintain as well.

In the old (v6) days text variable were restricted to 200 chars. Which made macro variables far more attractive when dealing with massive strings.