Deriving a new variable based on other variables

[Bounce]

Hi all,

I have a dataset with 5 variables: Date, ID, Account, Closed, Value and I am wanting to create a new variable: New_Value.

Basically New_Value is just the Value of the Account except if the Account for that specific ID has a Closed indicator of 'Y' AND is the latest recorded instance for that Account based on the Date, then I would like to change the New_Value for this row to 0.

For Example, New_Value should look like this:

Date                    ID       Account  Closed    Value     New_Value

01/01/2019          1             1            Y          $10             $0

25/12/2018          1             1            Y          $72             $72

22/11/2018          1             1            Y          $90             $90

20/11/2017          1             1            Y          $87             $87

There are many accounts per ID with each having multiple dates.

Many thanks in advance

[Jagadishkatam · Accepted solution]

please try

data have;     
input Date: ddmmyy10. ID Account Closed$ Value:dollar10.;
cards;
01/01/2019 1 1 Y $10 
25/12/2018 1 1 Y $72 
22/11/2018 1 1 Y $90 
20/11/2017 1 1 Y $87 
;

proc sort data=have;
by id account descending date ;
run;

data want;
set have;
by id account descending date;
if first.account and Closed='Y' then New_Value=0;
else New_Value=Value;
format date date9. value New_Value dollar10.;;
run;
              

[novinosrin]

data have;
input Date    :ddmmyy10.   ID       Account  Closed $   Value : dollar3. ;*   New_Value ;
format date date9.;
cards;
01/01/2019          1             1            Y          $10             $0
25/12/2018          1             1            Y          $72             $72
22/11/2018          1             1            Y          $90             $90
20/11/2017          1             1            Y          $87             $87
;

proc sql;
create table want as
select * , ifn(max(date)=date and closed='Y',0,value) as New_value
from have
group by id, account
order by id,account,date desc;
quit;