status changes over time

[Ronein]

Hello

I have the following input data set .

Each customer ID has follow up fields of 12 months.

First Field is Status1701  and  all customers are in status 0 in 1701 (Jan 2017) .

Status 0 means that customer is active in my department.

There are 2 more possible status levels:

status1- customer left the company

status2-customer left the department but is still in company.

The task is to find the status changes for each customer

This should be the desired calculated field "Conclusion":

ID     Conclusion
1       Alway 0
2       0 to 1 to 0
3       o to 1
4       o to 1
5       0 to 2
6      0 to 1 to 2

data tbl1;
input ID Status1701 Status1702 Status1703 Status1704 Status1705 Status1706
Status1707 Status1708 Status1709 Status1710 Status1711 Status1712;
cards;
1 0 0 0 0 0 0 0 0 0 0 0 0
2 0 0 0 0 0 0 1 1 1 1 1 0
3 0 1 1 1 1 1 1 1 1 1 1 1
4 0 0 0 0 0 0 0 0 0 0 1 1
5 0 0 0 2 2 2 2 2 2 2 2 2
6 0 0 0 0 0 0 0 0 2 2 1 1
;
Run;

Can anyone explain how to calculate "Conclusion" field in SAS?

We need to find out the status changes from one status to another status

Joey

[RW9]

Right, this gets you the changes per appearance in the data - note how I use a code window which is the {i} above post area to show code:

data tbl1;
  input ID Status1701 Status1702 Status1703 Status1704 Status1705 Status1706
           Status1707 Status1708 Status1709 Status1710 Status1711 Status1712;
cards;
1 0 0 0 0 0 0 0 0 0 0 0 0
2 0 0 0 0 0 0 1 1 1 1 1 0
3 0 1 1 1 1 1 1 1 1 1 1 1
4 0 0 0 0 0 0 0 0 0 0 1 1
5 0 0 0 2 2 2 2 2 2 2 2 2
6 0 0 0 0 0 0 0 0 2 2 1 1
;
run;

data want;
  set tbl1;
  length changes $20;
  array x{12} status1701--status1712;
  changes=strip(put(x{1},best.));
  curr=x{1};
  do i=2 to 12;
    if x{i} ne curr then do;
      curr=x{i};
      changes=catx(',',changes,strip(put(x{i},best.)));
    end;    
  end;
  changes=tranwrd(changes,","," to ");
run;

Note, your want in your post is incorrect, you say:

6      0 to 1 to 2

Which does not match the data which is 0-2-1

[Ronein]

Thank you so much!

It is a very clever and nice solution.

May I ask please:

strip(put(x{1},best.))
What is the reason of using strip function here?
What is the purpose of using best. format?

[RW9]

I use the best here just because I was being lazy, basically its a catch all for numeric formats.  And because I was lazy I then had to wrap a strip() function to remove any blanks that may have been added, for instance if the best format takes 8. then there would be 7 blanks.  If I wasn't being lazy I would look at my data and see it is all 1 number in length, so to acurately write this:

data tbl1;
  input ID Status1701 Status1702 Status1703 Status1704 Status1705 Status1706
           Status1707 Status1708 Status1709 Status1710 Status1711 Status1712;
cards;
1 0 0 0 0 0 0 0 0 0 0 0 0
2 0 0 0 0 0 0 1 1 1 1 1 0
3 0 1 1 1 1 1 1 1 1 1 1 1
4 0 0 0 0 0 0 0 0 0 0 1 1
5 0 0 0 2 2 2 2 2 2 2 2 2
6 0 0 0 0 0 0 0 0 2 2 1 1
;
run;

data want;
  set tbl1;
  length changes $24;
  array x{12} status1701--status1712;
  changes=put(x{1},1.);
  curr=x{1};
  do i=2 to 12;
    if x{i} ne curr then do;
      curr=x{i};
      changes=catx(',',changes,put(x{i},1.));
    end;    
  end;
  changes=tranwrd(changes,","," to ");
run;

Goes to show you can write code which works, but isn't optimal.  I also changed the length on changes as possibly 12 values with 11 commas. 

[Ksharp]

data tbl1;
  input ID Status1701 Status1702 Status1703 Status1704 Status1705 Status1706
           Status1707 Status1708 Status1709 Status1710 Status1711 Status1712;
cards;
1 0 0 0 0 0 0 0 0 0 0 0 0
2 0 0 0 0 0 0 1 1 1 1 1 0
3 0 1 1 1 1 1 1 1 1 1 1 1
4 0 0 0 0 0 0 0 0 0 0 1 1
5 0 0 0 2 2 2 2 2 2 2 2 2
6 0 0 0 0 0 0 0 0 2 2 1 1
;
run;
proc transpose data=tbl1 out=temp;
by id;
run;
data temp1;
 set temp;
 by id col1 notsorted;
 if first.col1;
 drop _name_;
run;
data want;
 length conclusion $ 200;
 do until(last.id);
  set temp1;
  by id;
  conclusion=catx(' to ',conclusion,col1);
 end;
 drop col1;
run;