BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ursula
Pyrite | Level 9

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

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

5 REPLIES 5
Patrick
Opal | Level 21

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
Pyrite | Level 9

Thank you very much, Patrick.

It works!Smiley Happy

Haikuo
Onyx | Level 15

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
Pyrite | Level 9

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
Calcite | Level 5

Ursula,

It is alias for have.

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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