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
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
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?
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.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: