BookmarkSubscribeRSS Feed
Siddharth123
Obsidian | Level 7

Hi All,

 

I am wondering if anyone has some sophisteicated ideas on generating insights from the toy data attached.

What I would like to do is some sort of trend analysis and find out -

 

1. How many times does the Status change from 1 to 0 and vis-a-vis. The idea is the larger the number the higher the instability. What I was thinking of is to have 2 coumns - for ID 0 and ID 1. For e.g. for ID 1, ID_1 = 1 but ID_0 = 0 as Status = 1 all time, for ID 2 it would be 2 for Status 1 and 1 for Status 0 as Status 1 was twice but 0 only once.

 

2. How many months did the Status remain unchanged before it got changed. For example, for ID 2, Status = 0 for May15 - Sep15 (5 months = 5) before it changes to 1.

 

Any more ideas welcome.

 

Kind regards

SK 

3 REPLIES 3
Reeza
Super User

How does your subject relate to your question? 

ballardw
Super User

it would really help to post your data in the form of a SAS data step.

Some of us cannot actually download files in Excel formats for security reasons and others won't because then we need to spend time turning them into SAS datasets with a potential of not actually matching your data due to the ambiguos nature of data in Excel (columns with mixed numeric and character data, merged cells, odd characters used to split lines within cells, summaries in rows and/or columns that involve data from other columns or rowws and other issues).

 

Also variable names become issues as all but one of the apparent "data" columns have at least one character that isn't valid in a SAS variable name.

 

Your data might be better transposed to ID Month and status (or what ever that 0/1 represents). Make sure the result is in the correct order. Then there are some things possible.

 

This code with a modified data set may do some of what you are asking. I'm not quite sure of all of the things you my be looking for in number 2 but this will find the longest unchanged period.

data have;
   input id Month monyy7. status;
   format Month MonYY7.;
datalines;
1 JAN2015 1
1 FEB2015 1
1 MAR2015 0
1 APR2015 0
1 MAY2015 0
1 JUN2015 0
1 JUL2015 0
1 AUG2015 1
1 SEP2015 1
1 OCT2015 1
1 NOV2015 1
1 DEC2015 1
2 JAN2015 1
2 FEB2015 1
2 MAR2015 1
2 APR2015 1
2 MAY2015 1
2 JUN2015 1
2 JUL2015 1
2 AUG2015 1
2 SEP2015 1
2 OCT2015 1
2 NOV2015 1
2 DEC2015 1
3 JAN2015 1
3 FEB2015 0
3 MAR2015 1
3 APR2015 0
3 MAY2015 1
3 JUN2015 0
3 JUL2015 1
3 AUG2015 0
3 SEP2015 1
3 OCT2015 0
3 NOV2015 1
3 DEC2015 0
;
run;


data want;
   set have;
   Lstatus= lag(status);
   by id;
   retain Unchanged;
   if first.id then do;
      change=0;
      unchanged =1;
   end;
   else do;
      change = (status ne Lstatus);
      if status=Lstatus then Unchanged=Unchanged +1;
      else Unchanged=1;
   end;
run;

proc tabulate data=want;
   class Id;
   var change unchanged;
   table id, 
         change='Times changed'*sum='' Unchanged='Longest Unchanged period'*max='';
run;
Ksharp
Super User

You'd better post the output you want to see .

 

data have;
   input id Month monyy7. status;
   format Month MonYY7.;
datalines;
1 JAN2015 1
1 FEB2015 1
1 MAR2015 0
1 APR2015 0
1 MAY2015 0
1 JUN2015 0
1 JUL2015 0
1 AUG2015 1
1 SEP2015 1
1 OCT2015 1
1 NOV2015 1
1 DEC2015 1
2 JAN2015 1
2 FEB2015 1
2 MAR2015 1
2 APR2015 1
2 MAY2015 1
2 JUN2015 1
2 JUL2015 1
2 AUG2015 1
2 SEP2015 1
2 OCT2015 1
2 NOV2015 1
2 DEC2015 1
3 JAN2015 1
3 FEB2015 0
3 MAR2015 1
3 APR2015 0
3 MAY2015 1
3 JUN2015 0
3 JUL2015 1
3 AUG2015 0
3 SEP2015 1
3 OCT2015 0
3 NOV2015 1
3 DEC2015 0
;
run;


data temp1;
   set have;
 if id=lag(id) and lag(status)=1 and status=0 then _1to0=1;
 if id=lag(id) and lag(status)=0 and status=1 then _0to1=1;
run;
proc summary data=temp1;
 by id;
 var _1to0 _0to1;
 output out=want1 sum=;
run;
proc print;run;




proc summary data=have ;
 by id status notsorted;
 output out=temp2;
run;
proc summary data=temp2;
 by id ;
 var _freq_;
 output out=want2 max=max;
run;
proc print;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
  • 3 replies
  • 1120 views
  • 0 likes
  • 4 in conversation