BookmarkSubscribeRSS Feed
Adnan_Razaq
Calcite | Level 5

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

5 REPLIES 5
Patrick
Opal | Level 21

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

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
Super User

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Rhodochrosite | Level 12

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

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
  • 804 views
  • 1 like
  • 5 in conversation