How to code the Minimum values

[ursula]

Hi,

I have question of how to code in SAS  for the minimum values (amount) up to the index year for each ID. see below for the example:

ID	year	amount	Minimum amount	Expected value
11	2001	100	Min(100) ---  year 2001	100
11	2002	25	Min(100,25)  -- year 2001 & 2002	25
11	2003	10	Min(100,25,10)  -- year 2001, 2002, 2003	10
11	2004	200	Min(100,25,10,200)	10
22	2002	50	Min(50)	50
22	2006	25	Min(50,25)	25
22	2007	200	Min(50,25,200)	25
22	2008	300	Min(50,25,200,300)	25

How to code it in data statement in order to get the Expected value.

Thank you very much in advance.

[Patrick · Accepted solution]

See below:

data have;

  infile datalines truncover dsd;

  input ID  year  amount;

  datalines;

11,2001,100

11,2002,25

11,2003,10

11,2004,200

22,2002,50

22,2006,25

22,2007,200

22,2008,300

;

run;

proc sort data=have;

  by id year;

run;

data want;

  set have;

  by id;

  retain min_amt;

  if first.id then min_amt=amount;

  else min_amt=min(min_amt, amount);

run;

[ursula]

Thank you very much, Patrick.

It works!

[Haikuo]

A Proc SQL alternative, no presort needed.

data have;

     infile cards dlm='09'x;

     input ID   year amount;

     cards;

11   2001 100 

11   2002 25  

11   2003 10  

11   2004 200 

22   2002 50  

22   2006 25  

22   2007 200 

22   2008 300 

;

/*sql*/

proc sql;

     create table want as

           select *, (select min(amount) from have where id=a.id and year <=a.year) as Rolling_min

                from have a;

quit;

[ursula]

Thank you, Hai Kuo.

This one using Proc sql also works!!

I'm not using Proc sql very much, but it's good to learn.

I wonder what does the "a" mean?

[kaushal2040]

Ursula,

It is alias for have.