Find out history status of particular column using BASE SAS Programming

[india2016]

Hello Team,

I am stucked at one task.

BELOW IS THE SAMPLE DATA-SET

Number Status         Date  

101        active        06/08/2018

101        Inactive     07/08/2018

101        inactive     08/08/2018

102        inactive     05/08/2018

102        active        06/08/2018

102        Inactive     07/08/2018

102        inactive     08/08/2018

Suppose today is 08/08/2018 and Today's status is "Inactive" then I need to find out  how many days the status is "Inactive" for same number?

I tried in multiple ways like giving flag, using LAG function but i dint crack the code.

Please suggest any path so that I can go through that.

Thanks in advance.

[LinusH]

Try to use RETAIN to keep the last active date within a Number BY group.

[Jagadishkatam]

please try the below code, if you need further modification, check the if then

data have;
input Number Status$         Date :ddmmyy10.;
status=propcase(status);
format date date9.;
cards; 
101        active        06/08/2018
101        Inactive     07/08/2018
101        inactive     08/08/2018
102        inactive     05/08/2018
102        active        06/08/2018
102        Inactive     07/08/2018
102        inactive     08/08/2018
;

proc sort data=have;
by number status;
run;

data want;
set have;
by number status;
retain new;
if first.status then new=date;
if status eq 'Inactive' then daysdiff=date-new;
format new date9.;
run;

[s_manoj]

Hi @india2016

data want (where = (name = "Inactive"));
set have;
by id name;
if first.name then start = date;
retain start;
if last.name  then do;
	end = date;
	duration = end - start + 1;
	output;
	end;
format start end date10.;
run;

Regards

Manoj

[s_lassen · Accepted solution]

Something like this may do:

data want;
  set have;
  by number;
  if first.number then
    days_inactive=0;
  if upcase(status)='ACTIVE' then
    days_inactive=0;
  else
    days_inactive+1;
run;