|
Hi,
From the other replies it looks like you've got a solution, but I also
recommend you look at Richard Devenezia's %seplist macro:
http://www.devenezia.com/downloads/sas/macros/index.php?m=seplist
Using it, your code would look something like:
proc sql;
create table temp as
select *
from a
where x in (%seplist(&twocharstring,nest=QQ));
quit;
You might have to put the macro invocation in %unquote, but it should get
you what you need.
I've found Richard's macro quite helpful.
HTH,
Scott
<ben.powell@CLA.CO.UK> wrote in message
news:200502091409.j19E9675004663@listserv.cc.uga.edu...
> Dear SAS-L
>
> I have an extract macro that takes a text variable that can either by
> missing (not entered) and hence equal to ""; a single two character string
> e.g. XX; or multiple two character strings e.g. XX YY ZZ.
>
> I want to pass that macro variable to an sql query where clause. I've
> incorporated error handling so the query only fires if the string ^ = ""
> but inorder to search on each of the two char strings they must be
> delimited by speechmarks.
>
> Call the var &twocharstring, say it equals XX YY ZZ. Say the sql query is
>
> proc sql;
> create table temp as
> select *
> from a
> where x in("&twocharstring");
> quit;
>
> The code I've used to parse &twocharstring and get the result XX" "YY" "ZZ
> is as below:
>
>
> <parse code>
> data temp;
> x = "&twocharstring";
> run;
>
> proc sql noprint;
> select length(x)
> into :leng
> from temp;
> drop table temp;
> quit;
>
> data temp;
> x = &leng;
> if x > 0 then do;
> if x = 2 then y = 2;
> else if x = 5 then y = 7;
> else if x > 5 and mod((x-5),3) = 0 then do;
> y = x + ((x-2)/3)*2;
> end;
> end;
> else call execute('%put ERROR: Twocharstring length range error;');
> drop x;
> run;
>
> proc sql noprint;
> select y
> into :y
> from temp;
> drop table temp;
> quit;
>
> data temp;
> length j $&y.;
> i = "&twocharstring";
> j = trim(tranwrd(i,' ','" "'));
> keep j;
> run;
>
> proc sql noprint;
> select j
> into :twocharstring
> from temp;
> drop table temp;
> quit;
> </parse code>
>
>
> There must be an easier way!
>
>
> Any comments much appreciated,
>
> Ben.
| 
