DATA Step, Macro, Functions and more

Minimum values by reporting date

Reply
Contributor
Posts: 25

Minimum values by reporting date

Hi All,

 

I have the following dataset containing rental values for property valuations

 

data have;
	input Account_Id$ received_date rental_value;
	informat received_date date9.;
	format received_date date9.;
	cards;
	12345 25JAN2008 500
	12345 30JUN2008 600
	12345 12DEC2008 400
	;
run;

What I need is to create another Monthly dataset with the minimum rental value for instance

 

 

Account_IDReporting_DateRental_value
12345Jan-08500
12345Feb-08500
12345Mar-08500
12345Apr-08500
12345May-08500
12345Jun-08500
12345Jul-08500
12345Aug-08500
12345Sep-08500
12345Oct-08500
12345Nov-08500
12345Dec-08400
12345Jan-09400

 

 

Many Thanks

 

Adnan

Respected Advisor
Posts: 4,702

Re: Minimum values by reporting date

[ Edited ]
Posted in reply to Adnan_Razaq

The basic approach could be to create a 2nd table with just month end dates and then to join this table with your Have table.

 

You will have to specify a bit more how your actual data could look like, i.e.

- Could there be more than one record per account_id and received_date?

       if yes: which date to chose for the month

- Why does in your sample result table the record with Reporting_Date Jun-08 have a Rental_Value of 500 and not of 600?

 

Contributor
Posts: 25

Re: Minimum values by reporting date

Hi,

 

There can be multiple records for account_id and received date. In those instances I would require to report the minimum rental valu.

 

I will need to report the minimum rental even though we receive a greater rental value.

 

Thanks

Super User
Posts: 9,941

Re: Minimum values by reporting date

Posted in reply to Adnan_Razaq

Use retain, the lag() function, by-group processing, the min() function, and a do loop, making use of the intnx() function for dates:

data have;
	input Account_Id$ received_date rental_value;
	informat received_date date9.;
	format received_date date9.;
	cards;
	12345 25JAN2008 500
	12345 30JUN2008 600
	12345 12DEC2008 400
	;
run;

data want;
set have (rename=(rental_value=_rental_value));
by account_id;
format reporting_date monyy7.;
retain rental_value;
lagdat = lag(received_date);
if first.account_id
then do;
  rental_value = _rental_value;
  reporting_date = intnx('month',received_date,0,'b');
  output;
end;
else do;
  reporting_date = intnx('month',lagdat,1,'b');
  do while (reporting_date < intnx('month',received_date,0,'b'));
    output;
    reporting_date = intnx('month',reporting_date,1,'b');
  end;
  reporting_date = intnx('month',received_date,0,'b');
  rental_value = min(rental_value,_rental_value);
  output;
end;
if last.account_id
then do;
  reporting_date = intnx('month',received_date,1,'b');
  output;
end;
drop
  received_date
  lagdat
  _rental_value
;
run;

proc print data=want noobs;
run;

Result:

Account_    reporting_    rental_
   Id          date        value

 	12345      JAN2008        500  
 	12345      FEB2008        500  
 	12345      MAR2008        500  
 	12345      APR2008        500  
 	12345      MAY2008        500  
 	12345      JUN2008        500  
 	12345      JUL2008        500  
 	12345      AUG2008        500  
 	12345      SEP2008        500  
 	12345      OCT2008        500  
 	12345      NOV2008        500  
 	12345      DEC2008        400  
 	12345      JAN2009        400  
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Super User
Posts: 9,465

Re: Minimum values by reporting date

Posted in reply to Adnan_Razaq

Use a retain and do loop.  However what I can't tell from your post is how far forward you want to go, your example want shows jan09, but this is indicated nowhere in the data?

data want;
  set have;
  retain lstdt;
  if _n_=1 then lstdt=received_date;
  else do;
    do i=0 to intck('month',lstdt,recieved_date);
new_date=intnx('month',lstdt,i); output; end; lstdt=recieved_date; end; format new_date date9.; run;

This will create outputs of rows for each month between last row and current.

Super Contributor
Posts: 373

Re: Minimum values by reporting date

Many answers here....and here's another one Smiley Happy

 

data want;
set have;
length month 8;
retain lowest_rental_value;
month = put(received_date, yymmn6.);
if _n_ = 1 then lowest_rental_value = 100000000;
if rental_value < lowest_rental_value then lowest_rental_value = rental_value;
run;

 

proc sql;
create table want as
select account_id, month, min(lowest_rental_value) as lowest_rental_value
from want
group by 1,2
;
quit;

 

//Fredrik

Ask a Question
Discussion stats
  • 5 replies
  • 134 views
  • 1 like
  • 5 in conversation