|
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);
hi, michael,
i would rather suggest not using or minimizing the macro. below is no macro
version of what you are doing and i think it is much clearer. about the only
part that is kind of boring to spell out is the renaming. For that, i
suggest a simple version of Ted Clay style pattern based list creation which
is shown at the bottom. hth.
cheers,
chang
/* test data */
data test;
do jj=1 to 10;
a=ranuni(1);
b=ranuni(1);
c=ranuni(1);
output;
end;
run;
proc standard data=test out=zscores mean=0 std=1 vardef=n;
var a b;
weight c;
run;
/* merge the standardized vars in */
data test1;
merge test zscores(rename=(a=stda b=stdb));
by jj;
run;
/* check */
proc print data=test1 noobs;
format a--stdb 4.2;
run;
/* on lst
jj a b c stda stdb
1 0.18 0.97 0.40 -1.4 1.66
2 0.26 0.92 0.97 -1.0 1.47
...
9 0.48 0.84 0.63 -.06 1.16
10 0.59 0.58 0.38 0.45 0.13
*/
/* Ted Clay style pattern based list creation */
%macro doOver(list, phrase=?, between=%str( ));
%local i item;
%let i = 1;
%let item = %scan(&list, &i);
%do %while (&item^=);
%*;%sysfunc(tranwrd(&phrase, ?, &item))
%let i = %eval(&i + 1);
%let item = %scan(&list, &i);
%if (&item^=) %then %*;≬
%end;
%mend doOver;
/* merge part re-written using %doOver macro */
%let rename = %doOver(a b, phrase=%str(?=std?));
data test2;
merge test zscores(rename=(&rename));
by jj;
run;
/* check */
proc compare base=test1 comp=test2;
run;
/* on log, in part
NOTE: No unequal values were found. All values compared are exactly equal.
*/
|
