Date:         Mon, 21 Apr 2008 10:42:45 -0400
Reply-To:     Paul Dorfman <sashole@BELLSOUTH.NET>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Paul Dorfman <sashole@BELLSOUTH.NET>
Subject:      Re: Dynamically write SQL statement -- dealing with commas?
Comments: To: Michael Murff <mysasbox@GMAIL.COM>

Mike,

You have accidentally misplaced %end in

>  %do iLoop=1 %to &ub;
>    %let vari=%qscan(&varlist,&iLoop);
>       new.&vari as std&vari
>    %end;
>  %if &iLoop lt &ub %then %do;
>     %str(,)
>  %end;

and thus are failing to generate a comma in the SELECT statement; code
instead

  %do iLoop=1 %to &ub;
     %let vari=%scan(&varlist,&iLoop);
      new.&vari as std&vari
      %if &iLoop lt &ub %then %do;
        %str(,)
      %end;
  %end ;

Also, to make it work, replace %qscan with %scan.

Kind regards
------------
Paul Dorfman
Jax, FL
------------


On Mon, 21 Apr 2008 08:40:06 -0500, Michael Murff <mysasbox@GMAIL.COM>
wrote:

>Hi,
>
>I'm having trouble getting my sql statement to dynamically write out with
>commas in the select clause. Keep getting some kind of spool error
probably
>having to do with quoting? If any one could help me out, would appreciate
>it.
>
>thx,
>
>Mike
>
>BTW - is there a way to get proc standard to create standarized variable
>with new names?
>
>option mlogic spool;
>*********************************************************************;
>* generate zscores which are interpreted as the num stdev from mean;
>*********************************************************************;
>%macro zscore(dsin=,dsout=,varlist=,wvar=,joinkey=);
>   proc standard data=&dsin mean=0 std=1 vardef=n out=zscores;
>     var &varlist;
>   %if &wvar ne %str() %then %do;
>     weight &wvar;
>   %end;
>   run;
>
>
>  proc sql;
>    create table &dsin as
>    select old.*,
>
>  %let count=0;
>  %do %while(%qscan(&varlist,&count+1,%str( )) ne %str());
>     %let count=%eval(&count+1);
>  %end;
>   %let ub=&count;
>  %do iLoop=1 %to &ub;
>    %let vari=%qscan(&varlist,&iLoop);
>       new.&vari as std&vari
>    %end;
>  %if &iLoop lt &ub %then %do;
>     %str(,)
>  %end;
>
>   from &dsin as old , zscores as new
>   where old.&joinkey.=new.&joinkey.;
>   quit;
>
>
>%mend zscore;
>
>data test;
>   do jj=1 to 10;
>    a=ranuni(1);
>    b=ranuni(1);
>    c=ranuni(1);
> output;
>   end;
>run;
>
>options mfile mprint;
>filename mprint 'no_mac.txt';
>
>%zscore(dsin=test,dsout=zscores,varlist=%quote(a b c) ,wvar=c,joinkey=jj);