treat last.observation of a group different in array (SAS 9.3 or 9.4)

[Sandra_L]

Hey guys,

i have a dataset where the observations are grouped by an ID (idgem) and each grout has observations for several years (jahr) ba halv month steps (jan1 - dec2) looks like this:

i wrote an array to find the local maximum (precipitaion data) in comparing each variable with the following and the previous variable.

the dec2 value is compared with the jan1 value of the following (by copying the column) year and jan1 is compared with dec2 of the previous year (by the lag function). 

now i have the problem with the first an the last year within a group (idgem).

how do i tell SAS to compare dec2 of the last year just to dec1 of the same year and ignore jan1 of the following year.

i tried with an if last.jahr then option, but it doesn't work. my code looks like this (the corresponding part is marked green at the end of it):

data test4;
 set mani;
   array local [24]; /* kreiert eine neue Datenmatrix mit den Überschriften local1-local24, ohne Werte, Variablen wird ein Indice zugewiesen in [] */
   do _n_ = 1 to 24;
   local[_n_] = "" ;
 end;
  array maxi [24] jan1 jan2 feb1 feb2 mar1 mar2 apr1 apr2 may1 may2 jun1 jun2 /* definiert quasi die Variablen des 'Quell-arrays' hier: maxi mit den Quelldaten */
                  jul1 jul2 aug1 aug2 sep1 sep2 oct1 oct2 nov1 nov2 dec1 dec2;
  do _n_ = 2 to 23;
    if (maxi [_n_-1] < maxi [_n_]) and (maxi [_n_]> maxi [_n_+1]) then local [_n_] = maxi [_n_];
    if maxi [_n_] = maxi [_n_-1] then local [_n_] = maxi [_n_];
    if maxi [_n_] = maxi [_n_+1] then local [_n_] = maxi [_n_];
   end;
  do _n_ = 1; /* lag Funktion vergleicht mit dem Wert dec2 der vorhergehenden Observation */
    if (maxi [_n_] > maxi [_n_+1]) and (maxi [_n_] > lag(dec2)) then local [_n_] = maxi [_n_];
    if (maxi [_n_] = maxi [_n_+1]) and (maxi [_n_] = lag(dec2)) then local [_n_] = maxi [_n_];
   end;
  do _n_= 24;
    if (maxi [_n_] > maxi [_n_-1]) and (maxi [_n_] > jan1follow) then local [_n_] = maxi [_n_];
    if (maxi [_n_] = maxi [_n_-1]) and (maxi [_n_] = jan1follow) then local [_n_] = maxi [_n_]; 
   end;
  if (last.jahr) then do _n_ = 24;
    if (maxi [_n_] > maxi [_n_-1]) then local [_n_] = maxi [_n_];
    if (maxi [_n_] = maxi [_n_-1]) then local [_n_] = maxi [_n_]; 
  end;
  if first.jahr then do _n_ = 1;
    if (maxi [_n_] > maxi [_n_+1]) then local [_n_] = maxi [_n_];
    if (maxi [_n_] = maxi [_n_+1]) then local [_n_] = maxi [_n_];
  end;
run; 

there is no error message but also no difference in the results!

any ideas?

greets,

Sandra

[Sandra_L]

o.k. i think i have to tell SAS to treat last.idgem different (not last.jahr)! but still it makes no difference

[RW9]

A simple change to the structure of your data will make your life a lot easier:

IDGEM       JAHR    HALV_MONTH  RESULT

0100100     1996     01JAN               1

0100100     1996     02JAN                0

...

Then you can simply group by whichever grouping you need.

[FreelanceReinh · Accepted solution]

Hallo Sandra,

Your programming logic, using first.idgem and last.idgem of course, looks correct to me (except that I'm not sure why in a situation like m4 = m5 < m6, value m5 should be regarded as a local maximum), but you have to insert a BY statement after the SET statement to make first./last.idgem work:

by idgem;

Whether the newly added (green) blocks of code then have an impact on the results, still depends on the data (in dataset MANI):

1. If the new IF conditions (regarding maxi[...]) are not met, the assignment statements for local[_n_] will not be executed. But in this case the corresponding assignment statements in the previous blocks of code have not been executed either, because the IF conditions there are stronger. (local[_n_] will be missing in this case.)
2. If (either of) the new IF conditions are met, the pertinent assignment statement will be executed. But in this case the corresponding identical assignment statement in the previous blocks of code might have been executed already: in the case that the additional condition (involving lag(dec2) or jan1follow, respectively) was met.
3. So, the only situation where the added code can influence the results is that (either of) the new IF conditions are met, whilst the corresponding additional condition was not satisfied.

[Sandra_L]

Hi Reinhard,
thak you for the lonx exlplication!
It was just the missing by statement, now it works nicely!
The local maximum for m5 in the situation m5<m6 is because m5 is the last value of the time series and m6 already belongs to a different station, so to make sure even if m5<m6 but m5>m4, that it declares m5 as max.
Thanks a lot,
Sandra