Referring to other observations when creating a new variable.

[art297]

Linlin, Matt already answered your question.  However, I, too, often don't know what the abbreviations mean that some people use.  I always look them up at: http://www.abbreviations.com/FWIW

[Linlin]

Thank you Art and Matt!

[Tom]

One thing that makes SAS so powerful a programming language is that you do not need to think of your operations in terms of iterations.  You can think of them as processes to apply to the dataset as a whole.

For this data you need to look through the 2010 data to find the largest value less than the current year's value.

So you can split out the 2010 records as another dataset and look through that as you process your original data.

proc sort data=input out=year2010(rename=(povratio=x perpovratio=y) drop=inty) ;
  where inty=2010;
  by descending povratio;
run;
data want ;
  set input ;
  if inty=2010 then do ;
    x=povratio; y=perpovratio ;
    output;
  end;
  else do p=1 to nobs until(x<povratio);
    set year2010 point=p nobs=nobs ;
    if x < povratio then output;
  end;
  if p > nobs then do ;
    x=.; y=.; output;
  end;
run;
proc compare data=want compare=output;
run;

Comparison of WORK.WANT with WORK.OUTPUT
(Method=EXACT)
Data Set Summary
Dataset               Created          Modified  NVar    NObs
WORK.WANT    09DEC11:01:33:05  09DEC11:01:33:05     5      16
WORK.OUTPUT  09DEC11:01:33:05  09DEC11:01:33:05     5      16
Variables Summary
Number of Variables in Common: 5.
Observation Summary
Observation      Base  Compare
First Obs           1        1
Last  Obs          16       16
Number of Observations in Common: 16.
Total Number of Observations Read from WORK.WANT: 16.
Total Number of Observations Read from WORK.OUTPUT: 16.
Number of Observations with Some Compared Variables Unequal: 0.
Number of Observations with All Compared Variables Equal: 16.
NOTE: No unequal values were found. All values compared are exactly equal.

[Linlin]

Hi Tom,

I didn't know we can use where statement in proc sort. Is this only avaliable in newer SAS versions?

Thanks!

[Tom]

If by new you mean some time in the last 10 to 15 years then it is new.

[FriedEgg]

data foo;

input inty povratio perpovratio;

datalines;

1990    4    10

1991    5    20

1992    2    30

1993    5    40

1994    6    50

1994    8    55

1995    3    60

1996    8    70

1997    7    80

1998    5    90

1999    8    95

2010    4.9    110

2010    1.6    120

2010    2.1    130

2010    7.9    140

2010    4.6    150

;

run;

data bar;

length inty povratio perpovratio x y 8;

if _n_=1 then do;

  declare hash f(dataset:'foo(where=(inty=2010) rename=(povratio=x perpovratio=y))',ordered:'a');

   f.definekey('x');

   f.definedata('x','y');

   f.definedone();

  declare hiter i('f');

end;

set foo;

  if inty=2010 then do;

   x=povratio; y=perpovratio;

   output;

  end;

  else do;

   i.first();

   do until(i.next()>0 or x>povratio); end;

   i.prev();

   output;

  end;

run;

inty    povratio    perpovratio     x      y
1990       4.0           10        2.1    130
1991       5.0           20        4.9    110
1992       2.0           30        1.6    120
1993       5.0           40        4.9    110
1994       6.0           50        4.9    110
1994       8.0           55        7.9    140
1995       3.0           60        2.1    130
1996       8.0           70        7.9    140
1997       7.0           80        4.9    110
1998       5.0           90        4.9    110
1999       8.0           95        7.9    140
2010       4.9          110        4.9    110
2010       1.6          120        1.6    120
2010       2.1          130        2.1    130
2010       7.9          140        7.9    140
2010       4.6          150        4.6    150