Prev: Proc gplot annotate question
Next: How does SAS determine the default length of a character
From: Kevin on 23 Oct 2009 10:51
Greetings All,

I am dealing with what appeared to be a very simple procedure of
adding observations in a previous row; turns out it is beyond my
capabilities in SAS.
I have tried various attempts with retain statements, lags, ^first.x,
etc. I cant seem to get this seemingly simple process.

I have a very simle dataset with 3 variables (L, i, and A). L and i
have many levels and A is a measured quantity. I am trying to
populate two new columns B and C.

B is the sum of the previous obs of both A and B. Clearly for the
first L within each i there will be no value for B - therfore it is
0. In the second L within each i, B is the sum of A and B from row 1
= 10, and so on within each i.

C is the sum of A and the previous obs of C. Again, the first L
within each i is special, it is simply the value of A. That is in row
1, C=A. In row 2, C (row2) =A (row2) and C (row1), and so on for each
L within each i.

The table below represents the process of populating B and C in Excel-
I am trying to recreate in SAS.

L i A B C
1 1 10 - 10
2 1 20 10 30
3 1 30 30 60
1 2 20 - 20
2 2 30 20 50
3 2 40 50 90
1 3 30 - 30
2 3 40 30 70
3 3 50 70 120


I am sure there is a very simple procedure of handling this situation
and I would appreciate any thoughts.

Cheers,
Kevin
From: jaheuk on 23 Oct 2009 11:02
use FIRST. and LAG1 functions !!


data test;
input l i a ;
cards;
1 1 10
2 1 20
3 1 30
1 2 20
2 2 30
3 2 40
1 3 30
2 3 40
3 3 50
;
run;

data test1;
set test;
by i l ;
b + lag1(a);
if first.i then b=.;
c = sum(a,b);
run;




Regards,
Herman


------------------------------------------------------------------------------------------------
On Oct 23, 4:51 pm, Kevin <kbos...(a)lsu.edu> wrote:
> Greetings All,
>
> I am dealing with what appeared to be a very simple procedure of
> adding observations in a previous row; turns out it is beyond my
> capabilities in SAS.
> I have tried various attempts with retain statements, lags, ^first.x,
> etc.  I cant seem to get this seemingly simple process.
>
> I have a very simle dataset with 3 variables (L, i, and A).  L and i
> have many levels and A is a measured quantity.  I am trying to
> populate two new columns B and C.
>
> B is the sum of the previous obs of both A and B.  Clearly for the
> first L within each i there will be no value for B - therfore it is
> 0.  In the second L within each i, B is the sum of A and B from row 1
> = 10, and so on within each i.
>
> C is the sum of A and the previous obs of C.  Again, the first L
> within each i is special, it is simply the value of A.  That is in row
> 1, C=A.  In row 2, C (row2) =A (row2) and C (row1), and so on for each
> L within each i.
>
> The table below represents the process of populating B and C in Excel-
> I am trying to recreate in SAS.
>
> L       i       A       B       C
> 1       1        10      -       10
> 2       1        20      10      30
> 3       1        30      30      60
> 1       2        20      -       20
> 2       2        30      20      50
> 3       2        40      50      90
> 1       3        30      -       30
> 2       3        40      30      70
> 3       3        50      70      120
>
> I am sure there is a very simple procedure of handling this situation
> and I would appreciate any thoughts.
>
> Cheers,
> Kevin

From: jaheuk on 23 Oct 2009 11:06
On Oct 23, 4:51 pm, Kevin <kbos...(a)lsu.edu> wrote:
> Greetings All,
>
> I am dealing with what appeared to be a very simple procedure of
> adding observations in a previous row; turns out it is beyond my
> capabilities in SAS.
> I have tried various attempts with retain statements, lags, ^first.x,
> etc.  I cant seem to get this seemingly simple process.
>
> I have a very simle dataset with 3 variables (L, i, and A).  L and i
> have many levels and A is a measured quantity.  I am trying to
> populate two new columns B and C.
>
> B is the sum of the previous obs of both A and B.  Clearly for the
> first L within each i there will be no value for B - therfore it is
> 0.  In the second L within each i, B is the sum of A and B from row 1
> = 10, and so on within each i.
>
> C is the sum of A and the previous obs of C.  Again, the first L
> within each i is special, it is simply the value of A.  That is in row
> 1, C=A.  In row 2, C (row2) =A (row2) and C (row1), and so on for each
> L within each i.
>
> The table below represents the process of populating B and C in Excel-
> I am trying to recreate in SAS.
>
> L       i       A       B       C
> 1       1        10      -       10
> 2       1        20      10      30
> 3       1        30      30      60
> 1       2        20      -       20
> 2       2        30      20      50
> 3       2        40      50      90
> 1       3        30      -       30
> 2       3        40      30      70
> 3       3        50      70      120
>
> I am sure there is a very simple procedure of handling this situation
> and I would appreciate any thoughts.
>
> Cheers,
> Kevin


============================================================
data test;
input l i a ;
cards;
1 1 10
2 1 20
3 1 30
1 2 20
2 2 30
3 2 40
1 3 30
2 3 40
3 3 50
;
run;

data test1;
set test;
by i l ;
b + lag1(a);
if first.i then b=.;

c = sum(a,b);

run;
=================================================
From: Muthia Kachirayan on 23 Oct 2009 11:25
Kevin,

I do not see why you need B to get C. It happens to be the cumulative values
of A within I ignoring L. If my observation is correct then this works. I
am not sure this is what you need.

data test;
input l i a ;
cards;
1 1 10
2 1 20
3 1 30
1 2 20
2 2 30
3 2 40
1 3 30
2 3 40
3 3 50
;
run;

data need;
do until(last.i);
set test;
by i;
c = sum(c, a);
output;
end;
run;

proc print data = need;
run;



On Fri, Oct 23, 2009 at 10:51 AM, Kevin <kboswe1(a)lsu.edu> wrote:

> Greetings All,
>
> I am dealing with what appeared to be a very simple procedure of
> adding observations in a previous row; turns out it is beyond my
> capabilities in SAS.
> I have tried various attempts with retain statements, lags, ^first.x,
> etc. I cant seem to get this seemingly simple process.
>
> I have a very simle dataset with 3 variables (L, i, and A). L and i
> have many levels and A is a measured quantity. I am trying to
> populate two new columns B and C.
>
> B is the sum of the previous obs of both A and B. Clearly for the
> first L within each i there will be no value for B - therfore it is
> 0. In the second L within each i, B is the sum of A and B from row 1
> = 10, and so on within each i.
>
> C is the sum of A and the previous obs of C. Again, the first L
> within each i is special, it is simply the value of A. That is in row
> 1, C=A. In row 2, C (row2) =A (row2) and C (row1), and so on for each
> L within each i.
>
> The table below represents the process of populating B and C in Excel-
> I am trying to recreate in SAS.
>
> L i A B C
> 1 1 10 - 10
> 2 1 20 10 30
> 3 1 30 30 60
> 1 2 20 - 20
> 2 2 30 20 50
> 3 2 40 50 90
> 1 3 30 - 30
> 2 3 40 30 70
> 3 3 50 70 120
>
>
> I am sure there is a very simple procedure of handling this situation
> and I would appreciate any thoughts.
>
> Cheers,
> Kevin
>
From: Kevin on 23 Oct 2009 12:05
On Oct 23, 10:06 am, jaheuk <hejac...(a)gmail.com> wrote:
> On Oct 23, 4:51 pm, Kevin <kbos...(a)lsu.edu> wrote:
>
>
>
>
>
> > Greetings All,
>
> > I am dealing with what appeared to be a very simple procedure of
> > adding observations in a previous row; turns out it is beyond my
> > capabilities in SAS.
> > I have tried various attempts with retain statements, lags, ^first.x,
> > etc.  I cant seem to get this seemingly simple process.
>
> > I have a very simle dataset with 3 variables (L, i, and A).  L and i
> > have many levels and A is a measured quantity.  I am trying to
> > populate two new columns B and C.
>
> > B is the sum of the previous obs of both A and B.  Clearly for the
> > first L within each i there will be no value for B - therfore it is
> > 0.  In the second L within each i, B is the sum of A and B from row 1
> > = 10, and so on within each i.
>
> > C is the sum of A and the previous obs of C.  Again, the first L
> > within each i is special, it is simply the value of A.  That is in row
> > 1, C=A.  In row 2, C (row2) =A (row2) and C (row1), and so on for each
> > L within each i.
>
> > The table below represents the process of populating B and C in Excel-
> > I am trying to recreate in SAS.
>
> > L       i       A       B       C
> > 1       1        10      -       10
> > 2       1        20      10      30
> > 3       1        30      30      60
> > 1       2        20      -       20
> > 2       2        30      20      50
> > 3       2        40      50      90
> > 1       3        30      -       30
> > 2       3        40      30      70
> > 3       3        50      70      120
>
> > I am sure there is a very simple procedure of handling this situation
> > and I would appreciate any thoughts.
>
> > Cheers,
> > Kevin
>
> ============================================================
> data test;
>  input l i a ;
> cards;
> 1       1        10
> 2       1        20
> 3       1        30
> 1       2        20
> 2       2        30
> 3       2        40
> 1       3        30
> 2       3        40
> 3       3        50
> ;
> run;
>
> data test1;
>  set test;
>   by i l  ;
>      b + lag1(a);
>   if first.i then b=.;
>
>   c = sum(a,b);
>
> run;
> =================================================- Hide quoted text -
>
> - Show quoted text -

Fantastic, thank you so much! I was in the neighborhood, but
definitely not there.

Much appreciated.

Kevin
 |  Next  |  Last
Pages: 1 2
Prev: Proc gplot annotate question
Next: How does SAS determine the default length of a character