Date:         Sun, 17 Sep 2000 11:40:49 -0400
Reply-To:     Richard DeVenezia <radevenz@IX.NETCOM.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Richard DeVenezia <radevenz@IX.NETCOM.COM>
Organization: MindSpring Enterprises
Subject:      Re: macro help

Brispac:

I doubt using SQL with an in clause containing tens of thousands of explicit
lookups is the proper way or efficient way to go.
A clause such as
...
and
T1.NDC in (select distinct <value> from <lookup-table> [ where ... ])
and
...
seems to me the more appropriate way.

In certain data configuration/table size/available index scenarios, a join
with the lookup-table and a 'top-level' equality check such as
...
and
T1.NDC =  lookup-table.column
and
...
is more efficient.

If you feel you absolutely must have an explicit list of such magnitude,
_don't_ use a macro variable, simply use a macro instead to deliver the text
to the SAS executor.

%* put in your own error checking and value quoting (based on vartype) as
needed;
%macro commaList (data=, var=);

  %local dsid varnum vartype getvar value comma;
  %let dsid = %sysfunc(open (&data));
  %if &dsid %then %do;
    %let varnum = %sysfunc (varnum (&dsid, &var));
    %if &varnum %then %do;
      %let vartype = %sysfunc (vartype(&dsid, &varnum));
      %let getvar = getvar&vartype;
      %let comma = 0;
      %do %while ( 0 = %sysfunc (fetch (&dsid)) );
         %let value = %sysfunc (&getvar (&dsid, &varnum));
         %if &comma %then
,          ;
         %else
            %let comma = 1;
&value
      %end;
    %end;
    %let dsid = %sysfunc (close (&dsid));
  %end;
%mend commaList;

data _null_;
 x = "
%commaList (data=xyz, var=i);
";
put x=;
run;

proc sql;
  select * from xyz where i in (%commaList (data=xyz, var=i));
quit;

--
Richard DeVenezia - try my new SAS Registry Browser under examples
SAS Macros and AF Tools - http://pweb.netcom.com/~radevenz
"Brispac" <brispac@frontiernet.net> wrote in message
news:8q0e47$17ps$1@node17.cwnet.frontiernet.net...
> THe problem is, a macro variable is limited to 64K and this method works
> fine when I had a couple hundred observations but i am dealing with ~13000
> observations so I reach the limit. I was looking for a macro to do this so
I
> could use it in my SQL. Something like:
> Proc SQL;
> Select <fields here>
> From <tables here>
> Where
> <where criteria> and
> T1.NDC in (%macro here);
> quit;
>
> I am trying this from a SUGI paper from 96 and dont know if it works since
I
> am also new to SQL.
>
> Thanks!
>
> Peter Crawford wrote in message ...
> >>"Brispac" <brispac@frontiernet.net> wrote:
> >>>
> >>> I hope someone can be of help. I would like a macro that will
transform
> a
> >>> column of numbers into a string with quotation marks and separated by
> >>> a comma with a last variable that has no comma.
> >
> >.......Use the quote() function in v6, or quote(trim()) in v8   +++
> >
> >
> >(following the signature below) Roland
<roland.rashleigh-berry@virgin.net>
> >has done much of the work.
> >
> >
> >You just need to add a little sql to make that macro variable.
> >(I think sql is easier for this than a data step or macro processing )
> >
> >
> >First Roland's data step to load the list (but I've assumed $character)
> >
> >data cards;
> > *not knowing the longest length your strings might become;
> > length str $1000;
> >  input str ;
> >  cards;
> >11111
> >22222
> >33333
> >44444
> >55555
> >;
> >
> >
> >*Now make the macro variable in the current environment ;
> >%let comDlist=empty ; *** empty comma delimited list ;
> >
> >
> >*Finally use the sql clause    into :macroVar
> > This is well worth reading up on, in the on-line help or doc;
> >
> >proc sql noprint;
> > select quote( trim( str )) into :comDlist separated by ', '
> >   from work.cards
> >     ;
> >***Nearly always I need the count of items in the list and
> >   this is simplest at this stage !!! ;
> >  %let numDlist = &sqlobs;
> >quit;
> >
> >%put INFO: &numDlist items in list &comdlist ;
> >
> >
> >My v8.0 log reported this
> >INFO: 5 items in list "11111", "22222", "33333", "44444", "55555"
> >
> >
> >
> >Please note that for v8, we need the trim() function inside the quote().
> >But v6 would have needed only
> > select quote( str ) into :comDlist separated by ', ' from work.cards;
> >
> >For those who voted in favour of the change in behaviour of quote()
> >:-(
> >
> >Regards
> >--
> >Peter Crawford
> >Roland <roland.rashleigh-berry@virgin.net> writes
> >>I don't have a version of SAS on this PC but here goes:
> >>
> >>filename xxxx "C:\fff\ggg" new;
> >>
> >>data cards;
> >>  input str;
> >>cards;
> >>11111
> >>22222
> >>33333
> >>44444
> >>55555
> >>;
> >>
> >>data  _null_;
> >>  file xxxx notitles noprint;
> >>  set cards end=last;
> >>  put "'" str +(-1) "'" @;
> >>  if last then do;
> >>    put;
> >>  end;
> >>  else do;
> >>    put "," @;
> >>  end;
> >>run;
> >>
> >>
> >>Brispac wrote:
> >>>
> >>> I hope someone can be of help. I would like a macro that will
transform
> a
> >>> column of numbers into a string with quotation marks and separated by
a
> >>> comma with a last variable that has no comma. So, if my input dataset
> looks
> >>> like:
> >>>
> >>> 11111
> >>> 22222
> >>> 33333
> >>> 44444
> >>> 55555
> >>> ...
> >>> XXXX
> >>>
> >>> I would like to get :
> >>> '11111','22222','33333','44444','55555',....'XXX','99999'
> >>>
> >>> Is there a straightforward way to do this? I thought of using a data
> _null_
> >>> step to create a macro variable that represents the total number of
> >>> variables and then macro variables for each variable. That seems to
work
> but
> >>> I dont know how to add the quotation marks and the commas.
> >>>
> >>> Thanks