Date: Wed, 15 Mar 2006 17:48:35 +0000
Reply-To: toby dunn <tobydunn@HOTMAIL.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: toby dunn <tobydunn@HOTMAIL.COM>
Subject: Re: SQL vs Data Step (was Sum a column)
Content-Type: text/plain; format=flowed
Your welcome. Its a Howard interleaving a data set back on itself thingy.
As for the SQL taking longer to run, well youd have to ask Howard or Sig as
the mysteries of the SQL optimizer still confuse me. Perhaps there is some
way to rewrite the SQL to make it run faster, but I'll be danged if I know
what it is.
From: Arthur Tabachneck <art297@NETSCAPE.NET>
Reply-To: Arthur Tabachneck <art297@NETSCAPE.NET>
Subject: SQL vs Data Step (was Sum a column)
Date: Wed, 15 Mar 2006 11:51:54 -0500
First, thanks for teaching me something new! After seeing that your data
step solution actually worked, I modified your example, slightly, and
increased the number of records to 5,000,000.
To my surprise, the data step approach had real times of between 3.15 and
3.17 seconds, while the SQL approach had times ranging between 5.48 and
The code I tested was:
input id $ empl;
do i=1 to 1000000;
create table Result as
select *, sum(empl) as sum
set temp (in=a) temp (in=b);
if a then sum+empl;
On Wed, 15 Mar 2006 15:56:46 +0000, toby dunn <tobydunn@HOTMAIL.COM> wrote:
>Well SQL would be the easiest solution Jiann I think did that one and for
>those who are SQL adverse there us always a data step solution:
>data one ;
>infile cards ;
>input id empl ;
>data two ;
>set one ( in = First )
> one ( in = Second ) ;
>if First then do ;
> Sum + Empl ;
>if Second then output ;
>data = two ;
>From: RasmusJo <rjo.cebr@CBS.DK>
>Reply-To: RasmusJo <rjo.cebr@CBS.DK>
>Subject: Sum a column
>Date: Wed, 15 Mar 2006 07:22:42 -0800
>I'm having a brain melt-down as I try to sum a column/variable.
>My problem is that I would like to sum the variables empl, see below:
>such that I get the variable 'sum'
>id empl sum
>1 1 7
>2 1 7
>3 2 7
>4 2 7
>5 1 7
>So far I only managed to create an accumulating variable whose value is
>7 for the last observation.However, I would like to create a variable
>that is 7 for all observations.
>Can anyone help`?