DATA Step, Macro, Functions and more

ARRAY

Reply
Contributor
Posts: 50

ARRAY

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 

Super User
Posts: 17,784

Re: ARRAY

How does your subject relate to your question? 

Super User
Posts: 10,483

Re: ARRAY

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;
Super User
Posts: 9,671

Re: ARRAY

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;
Ask a Question
Discussion stats
  • 3 replies
  • 544 views
  • 0 likes
  • 4 in conversation