topic Re: How to code the Minimum values in SAS Programming

How to code the Minimum values

ursula — Wed, 26 Nov 2014 18:16:13 GMT

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.

Re: How to code the Minimum values

Patrick — Wed, 26 Nov 2014 18:23:32 GMT

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;

Re: How to code the Minimum values

Haikuo — Wed, 26 Nov 2014 18:25:01 GMT

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;

Re: How to code the Minimum values

ursula — Wed, 26 Nov 2014 18:33:45 GMT

Thank you very much, Patrick.

It works!

Re: How to code the Minimum values

ursula — Wed, 26 Nov 2014 18:48:35 GMT

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?

Re: How to code the Minimum values

kaushal2040 — Wed, 26 Nov 2014 19:10:43 GMT

Ursula,

It is alias for have.