Meteorite | Level 14

## status changes over time

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

4 REPLIES 4
Diamond | Level 26

## Re: status changes over time

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;```

6      0 to 1 to 2

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

Meteorite | Level 14

## Re: status changes over time

Thank you so much!

It is a very clever and nice solution.

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

## Re: status changes over time

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.

Super User

## Re: status changes over time

``````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;``````
Discussion stats
• 4 replies
• 1023 views
• 1 like
• 3 in conversation