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.
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;
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;
Thank you very much, Patrick.
It works!
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;
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?
Ursula,
It is alias for have.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.