How to code the Minimum values

Accepted Solution Solved
Reply
Contributor
Posts: 64
Accepted Solution

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:

IDyearamountMinimum amountExpected value
112001100Min(100) ---  year 2001100
11200225Min(100,25)  -- year 2001 & 200225
11200310Min(100,25,10)  -- year 2001, 2002, 200310
112004200Min(100,25,10,200) 10
22200250Min(50)50
22200625Min(50,25)25
222007200Min(50,25,200)25
222008300Min(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
Respected Advisor
Posts: 3,831

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;

View solution in original post


All Replies
Solution
‎11-26-2014 01:23 PM
Respected Advisor
Posts: 3,831

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;

Contributor
Posts: 64

Re: How to code the Minimum values

Thank you very much, Patrick.

It works!Smiley Happy

Respected Advisor
Posts: 3,124

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;

Contributor
Posts: 64

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 195 views
  • 6 likes
  • 4 in conversation