COMPGROUPS.NET | Search | Post Question | Groups | Stream | About | Register

### DOW Loop with multiple sum statements within by-group

• Follow

```I have financial data sorted by Stock_Code and Date. Each Stock_Code
by-group has between 0 and 19 announcement dates, approximately 6
months apart. Announcement dates differ within a by-group and between
Stock_Codes.

Below is an example Timeline for each Stock_Code By-group:
First.Stock_Code.......Announcement.......Announcement (up to
19).........Last.Stock_Code.

For each Stock_Code by-group I want to cumulatively sum for each
observation: N, Information and Price between announcement dates. N
and Information are natural numbers.

The SAS code below began as an unsuccessful attempt to modify and
extend Howard Schreier=92s code in the thread =93Jump Out from DO Loop?=94.

Data OUT1 ;
Do until  (Last.Stock_Code ) ;
Set IN ;
By Stock_Code DATE;
If (Announcement =3D . ) then do;
Sum_N + N ;
Sum_Information + Information ;
Sum_Price + Price;
Output Out1;
End;
Else if (Announcement ne . ) then do;
Sum_N=3D N;
Sum_Information =3D Information ;
Sum_Price =3D Price ;
Output out1;
End;
End ;
Run;

The output I get is that:
Sum_Information and Sum_Price don=92t sum, but equal Information and
Price respectively.
Sum_N cumulatively sums N as a one total at the end of the by-group
for the duration of the next by-group.

Thanks in advance for any suggestions.

```
 0
Reply ma_ke2 (3) 5/25/2010 5:33:51 AM

```On May 24, 10:33=A0pm, "Mark@UTS" <ma_...@yahoo.com> wrote:
> I have financial data sorted by Stock_Code and Date. Each Stock_Code
> by-group has between 0 and 19 announcement dates, approximately 6
> months apart. Announcement dates differ within a by-group and between
> Stock_Codes.
>
> Below is an example Timeline for each Stock_Code By-group:
> First.Stock_Code.......Announcement.......Announcement (up to
> 19).........Last.Stock_Code.
>
> For each Stock_Code by-group I want to cumulatively sum for each
> observation: N, Information and Price between announcement dates. N
> and Information are natural numbers.
>
> The SAS code below began as an unsuccessful attempt to modify and
> extend Howard Schreier=92s code in the thread =93Jump Out from DO Loop?=
=94.
>
> Data OUT1 ;
> Do until =A0(Last.Stock_Code ) ;
> Set IN ;
> By Stock_Code DATE;
> If (Announcement =3D . ) then do;
> =A0 =A0 =A0Sum_N + N ;
> =A0 =A0 =A0Sum_Information + Information ;
> =A0 =A0 =A0Sum_Price + Price;
> =A0 =A0 =A0Output Out1;
> End;
> Else if (Announcement ne . ) then do;
> =A0 =A0 =A0 Sum_N=3D N;
> =A0 =A0 =A0 Sum_Information =3D Information ;
> =A0 =A0 =A0 Sum_Price =3D Price ;
> =A0 =A0 =A0 Output out1;
> End;
> End ;
> Run;
>
> The output I get is that:
> Sum_Information and Sum_Price don=92t sum, but equal Information and
> Price respectively.
> Sum_N cumulatively sums N as a one total at the end of the by-group
> for the duration of the next by-group.
>
> Thanks in advance for any suggestions.

step.

I'm assuming your do loop should start AFTER you set IN the data step.
IN is also a reserved word so I'd consider labelling my dataset
something else.

Data OUT1 ;
retain sum_information 0 sum_price 0 sum_n 0;
Set IN ;
By Stock_Code DATE;
Do until  (Last.Stock_Code ) ;
If (Announcement =3D . ) then do;
Sum_N + N ;
Sum_Information + Information ;
Sum_Price + Price;
Output Out1;
End;
Else if (Announcement ne . ) then do;
Sum_N=3D N;
Sum_Information =3D Information ;
Sum_Price =3D Price ;
Output out1;
End;
End ;
Run;

HTH,

```
 0

```Thanks Reeza,
the original code was a Dow loop, which put the "do until" loop
between the Data and Set statements.
I'm having trouble with Retain statements, so thanks again for
hightlighting the issue.

```
 0

```Mark -
It is not clear what you are trying to do.
If you want to summarize by stock_code then use PROC SUMMARY with a
BY statement.
If you want to add the summary information for the whole stock_code
to each individual observations you have two choices.
1) Generate the summary and re-merge with the individual. You can use
PROC SUMMARY and a MERGE or use PROC SQL .
2) Use the DoWhile loop. In this case you will need TWO loops in the
same dataset . The first to generate the summary stats and the second
to generate the observations.
data want;
do until (last.stock_code);
set in;
by stock_code;
sum_n+n;
end;
do until (last.stock_code);
set in;
by stock_code;
output;
end;
run;

I have no idea what the test for missing announcement was in your
original code as I did not see anything in the problem description

- Tom
On May 26, 11:40=A0pm, "Mark@UTS" <ma_...@yahoo.com> wrote:
> Thanks Reeza,
> the original code was a Dow loop, which put the "do until" loop
> between the Data and Set statements.
> I'm having trouble with Retain statements, so thanks again for
> hightlighting the issue.

```
 0

```Thanks Tom,
I still need to master the art of asking SAS questions with clarity.
Pleased to see Dow loops haven't gone out of fashion.

```
 0

4 Replies
236 Views

Similiar Articles:

7/27/2012 6:29:12 AM