Date:         Sun, 19 Apr 2009 17:59:28 -0400
Reply-To:     msz03@albany.edu
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Mike Zdeb <msz03@ALBANY.EDU>
Subject:      Re: Chance to Make SAS-L History: Did You Know That...
Content-Type: text/plain;charset=iso-8859-1

hi ... these postings reminded me of how after reading Christiana Williams neat paper "PROC SQL for DATA Step Die-hards"

http://www.nesug.org/Proceedings/nesug05/how/how7.pdf

that my immediate reaction (as a data step die-hard) was that it would be good to learn a bit more about SQL

I realize that the main point of the posting was to discuss "beyond double-DOW", but
I think that this fits Art's comment in the first posting ...

"Yes, of course, some SAS procs could accomplish the job more easily than the following code."


proc sql;
create table zscores as
select *, (height - mean(height)) / std(height) as z
from sashelp.class
group by sex;
quit;

--
Mike Zdeb
U@Albany School of Public Health
One University Place
Rensselaer, New York 12144-3456
P/518-402-6479 F/630-604-1475


> Actually, after re-reading Koen and Paul's paper, they did indeed point out
> that the DOW loop could be extended beyond just a double-DOW.
>
> I also realized that my suggested code wasn't quite as tight as it should
> have been.  Therefore, below is the same solution, but using the same
> concepts that Paul and Koen had suggested in their paper:
>
> proc sort data=sashelp.class out=have;
>   by sex;
> run;
>
> data want (drop=sum: n);
>   do until(last.sex);
>     set have;
>     by sex;
>     if missing(height) then Continue;
>     sum=sum(sum,height);
>     n=sum(n,1);
>   end;
>
>   do until(last.sex);
>     set have;
>     by sex;
>     if missing(height) then Continue;
>     sum2=sum(sum2,(height-sum/n)**2);
>   end;
>
>   do until(last.sex);
>     set have;
>     by sex;
>     if missing(height) then Continue;
>     z=(height-sum/n)/sqrt(sum2/(n-1));
>     output;
>   end;
> run;
>
> Art
> --------
> On Fri, 17 Apr 2009 20:41:13 -0400, Arthur Tabachneck <art297@NETSCAPE.NET>
> wrote:
>
>>Did you know that the DOW and Double-DOW can be extended to Tripple-DOW or
>>even more complex configurations?
>>
>>At the last SGF I was fortunate to have taken in Paul Dorfman's
>>presentation on what goes on, under the hood, when utilizing a Double-DOW
>>approach.
>>
>>After all these years, the logic finally came through (i.e.,
> "lightbulb!").
>>
>>A z-score shows how many standard deviations a score is from the mean.
>>What if you wanted to know the height z-score, for male and female
>>subjects, relative to the subjects' sex group mean score?
>>
>>Yes, of course, some SAS procs could accomplish the job more easily than
>>the following code, but I, personally, was amazed that the multiple DOW
>>approach could be extended beyond double processing (note: if you want to
>>see what's going on, under the hood, delete the asterisks that precede the
>>put statements):
>>
>>proc sort data=sashelp.class out=have;
>>  by sex;
>>run;
>>
>>data want;
>>  retain sd average;
>>  do until(last.sex);
>>    set have;
>>    by sex;
>>    if first.sex then do;
>>      call missing(sum);
>>      call missing(n);
>>    end;
>>    if not(missing(height)) then do;
>>      sum+height;
>>      n+1;
>>    end;
>>    *put "first-loop: " _all_;
>>  end;
>>
>>  do until(last.sex);
>>    set have;
>>    by sex;
>>    if first.sex then do;
>>      average=sum/n;
>>      call missing(sum2);
>>    end;
>>    if not(missing(height)) then do;
>>      sum2+(height-average)**2;
>>    end;
>>    *put "second-loop: " _all_;
>>  end;
>>
>>  do until(last.sex);
>>    set have;
>>    by sex;
>>    if first.sex then do;
>>      sd=sqrt(sum2/(n-1));
>>    end;
>>    if not(missing(height)) then do;
>>      z=(height-average)/sd;
>>    end;
>>    output;
>>    *put "third-loop: " _all_;
>>  end;
>>run;
>>
>>Art
>