## How to code the Minimum values

Solved
Frequent Contributor
Posts: 92

# How to code the Minimum values

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.

Accepted Solutions
Solution
‎11-26-2014 01:23 PM
Posts: 4,736

## Re: How to code the Minimum values

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;

All Replies
Solution
‎11-26-2014 01:23 PM
Posts: 4,736

## Re: How to code the Minimum values

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;

Frequent Contributor
Posts: 92

## Re: How to code the Minimum values

Thank you very much, Patrick.

It works!

Posts: 3,167

## Re: How to code the Minimum values

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;

Frequent Contributor
Posts: 92

## Re: How to code the Minimum values

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?

Contributor
Posts: 50

## Re: How to code the Minimum values

Ursula,

It is alias for have.

🔒 This topic is solved and locked.