CODEWITHSUNDEEP
sasreset
Eric
Eric

Reputation: 1

SAS Change the first record

Let's imagine the following table ...

obs State Imp i i2
1 me 100 100
2 me 90 100 100
3 me 80 100 100
4 me 70 100 100
5 me 1000 1000 100
6 me 900 1000 1000
7 me 800 1000 1000
8 me 0 1000 1000
9 me 2000 2000 1000
10 me 1900 2000 2000
11 gu 20 2000 2000
12 ca 40 2000 2000
13 ca 50 2000 2000
14 ca 30 2000 2000
15 ca 10 2000 2000

as you can see column "i2" is lag (i). What I want to do is: 1.- column "i" finds the maximum value as it progresses, so i want to reset that column "i" every first "state", in order to get that maximum value of each state. 2.- modify the column "i2" so that it is as follows: that each first value of "State" (obs 1-me, 11-gu and 12-ca) column "i" has the value of column "imp"

obs State Imp i i2
1 me 100 100 100
2 me 90 100 100
3 me 80 100 100
4 me 70 100 100
5 me 1000 1000 100
6 me 900 1000 1000
7 me 800 1000 1000
8 me 0 1000 1000
9 me 2000 2000 1000
10 me 1900 2000 2000
11 gu 20 20 20
12 ca 40 40 40
13 ca 50 50 40
14 ca 30 50 50
15 ca 10 50 50

i have tried with this code, but it doesn't work

data metodo;
set sa80;
    retain i;
    if first.state then i=max(imp);
    else i = max(imp,i);
    i2 = lag(i);
run;

data final;
set metodo;
    retain i2_aux;
    if first.state then i2_aux = total;
    else i2_aux = i2;
run;

Hope you could help, and thank you in advance

Upvotes: 0

Views: 186

Answers (1)

Tom
Tom

Reputation: 51566

The main thing it not use an existing variable as the new RETAINed variable because then each time the SET statement executes the value retained is replaced with the value read from the input.

It also helps if the data is sorted by the key variable, although you can use the NOTSORTED keyword on the BY statement to process grouped, but not sorted, data.

data have;
  input state $ imp ;
cards;
ca 40
ca 50
ca 30
ca 10
gu 20
me 100
me 90
me 80
me 70
me 1000
me 900
me 800
me 0
me 2000
me 1900
;

data want;
  set have ;
  by state notsorted;
  retain i;
  i=max(i,imp);
  if first.state then i=imp;
  i2=lag(i);
  if first.state then i2=imp;
run;

Results:

Obs    state     imp       i     i2

  1     ca        40      40      40
  2     ca        50      50      40
  3     ca        30      50      50
  4     ca        10      50      50
  5     gu        20      20      20
  6     me       100     100     100
  7     me        90     100     100
  8     me        80     100     100
  9     me        70     100     100
 10     me      1000    1000     100
 11     me       900    1000    1000
 12     me       800    1000    1000
 13     me         0    1000    1000
 14     me      2000    2000    1000
 15     me      1900    2000    2000

Fixed order of resetting I and LAG(I) call.

Upvotes: 1

Related Questions