BookmarkSubscribeRSS Feed
Ronein
Meteorite | Level 14

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
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Meteorite | Level 14

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Super User
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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 890 views
  • 1 like
  • 3 in conversation