Retain and max() ??
in [SAS]
|
Prev: Enterprise Guide 4.1/SAS 9.1.3 - How to disable LIBNAME statement
Next: How to pass dataset variables' value to macro
From: Villerwalle on 2 Jun 2010 11:03 I have a data set like this data have ; input id date x ; datalines ; 101 . 12 101 1 . 101 2 . 101 . 10 101 . 25 101 . 17 101 3 . 101 4 . ; If date exists x is missing and vice versa. Now I need a new variable Value as below. Value should be max of the current x and last Value if date is missing more than once consecutively. data want ; input id date x Value ; 101 . 12 12 101 1 . 12 101 2 . 12 101 . 10 10 101 . 25 25 101 . 17 25 101 3 . 25 101 4 . 25 101 . 8 8 Thankful for suggestions /Lars Wahlgren
From: Arthur Tabachneck on 2 Jun 2010 17:57 Lars, I'm not sure why you want to do this, and the last record of your want file I can only guess is coming from an extra record that wasn't in your have file. If so, then something like the following appears to produce the output you wanted. P.S. There likely is a more direct way of accomplishing the task. data want (drop=last_value lag); set have; by id; retain last_value; lag=lag(x); if first.id then call missing(last_value); if not(missing(date)) then value=last_value; else do; if missing(lag) then call missing(last_value); value=max(x,last_value); end; last_value=value; run; HTH, Art ------------- On Jun 2, 11:03 am, Villerwalle <villerwa...(a)gmail.com> wrote: > I have a data set like this > data have ; > input id date x ; > datalines ; > 101 . 12 > 101 1 . > 101 2 . > 101 . 10 > 101 . 25 > 101 . 17 > 101 3 . > 101 4 . > ; > If date exists x is missing and vice versa. > > Now I need a new variable Value as below. > Value should be max of the current x and last Value if date is missing > more than once consecutively. > > data want ; > input id date x Value ; > 101 . 12 12 > 101 1 . 12 > 101 2 . 12 > 101 . 10 10 > 101 . 25 25 > 101 . 17 25 > 101 3 . 25 > 101 4 . 25 > 101 . 8 8 > > Thankful for suggestions > /Lars Wahlgren
From: Villerwalle on 9 Jun 2010 05:18
Thanks Arthur, it worked just as I (or rather a student of mine) wanted it to. /Lars Wahlgren On 2 Juni, 23:57, Arthur Tabachneck <art...(a)netscape.net> wrote: > Lars, > > I'm not sure why you want to do this, and the last record of your want > file I can only guess is coming from an extra record that wasn't in > your have file. If so, then something like the following appears to > produce the output you wanted. P.S. There likely is a more direct > way of accomplishing the task. > > data want (drop=last_value lag); > set have; > by id; > retain last_value; > lag=lag(x); > if first.id then call missing(last_value); > if not(missing(date)) then value=last_value; > else do; > if missing(lag) then call missing(last_value); > value=max(x,last_value); > end; > last_value=value; > run; > > HTH, > Art > ------------- > On Jun 2, 11:03 am,Villerwalle<villerwa...(a)gmail.com> wrote: > > > > > I have a data set like this > > data have ; > > input id date x ; > > datalines ; > > 101 . 12 > > 101 1 . > > 101 2 . > > 101 . 10 > > 101 . 25 > > 101 . 17 > > 101 3 . > > 101 4 . > > ; > > If date exists x is missing and vice versa. > > > Now I need a new variable Value as below. > > Value should be max of the current x and last Value if date is missing > > more than once consecutively. > > > data want ; > > input id date x Value ; > > 101 . 12 12 > > 101 1 . 12 > > 101 2 . 12 > > 101 . 10 10 > > 101 . 25 25 > > 101 . 17 25 > > 101 3 . 25 > > 101 4 . 25 > > 101 . 8 8 > > > Thankful for suggestions > > /Lars Wahlgren |