Minimum values by reporting date

[Adnan_Razaq]

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_ID	Reporting_Date	Rental_value
12345	Jan-08	500
12345	Feb-08	500
12345	Mar-08	500
12345	Apr-08	500
12345	May-08	500
12345	Jun-08	500
12345	Jul-08	500
12345	Aug-08	500
12345	Sep-08	500
12345	Oct-08	500
12345	Nov-08	500
12345	Dec-08	400
12345	Jan-09	400

Many Thanks

Adnan

[Patrick]

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?

[Adnan_Razaq]

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

[Kurt_Bremser]

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  

[RW9]

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.

[FredrikE]

Many answers here....and here's another one 🙂

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