topic Re: Find out history status of particular column using BASE SAS Programming in SAS Programming

Find out history status of particular column using BASE SAS Programming

india2016 — Tue, 14 Aug 2018 05:55:39 GMT

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.

Re: Find out history status of particular column using BASE SAS Programming

LinusH — Tue, 14 Aug 2018 06:17:30 GMT

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

Re: Find out history status of particular column using BASE SAS Programming

Jagadishkatam — Tue, 14 Aug 2018 06:19:37 GMT

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;

Re: Find out history status of particular column using BASE SAS Programming

s_manoj — Tue, 14 Aug 2018 06:48:45 GMT

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

Re: Find out history status of particular column using BASE SAS Programming

s_lassen — Tue, 14 Aug 2018 07:17:05 GMT

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;