by process to get transition count

[YW_CA]

how to get the count of swtiched ON by light by day; I have a dataset with a light no.. I need get a count of how many times the light is switched on in the set.  for example, I want to return the results that the light_no. 1 was swtiched on 2 times in the dataset. 

Light_no.   HOUR ON_OFF_STATUS

1                  1         OFF

1                  2         OFF

1                  3         ON

1                  4         ON

1                  5        OFF

1                  6        ON

this is data step I am using. getting weird results. Any idea? 

data ct;

set temp;

where ON_OFF_STATUS = ON;

by light_no. ON_OFF_STATUS notsorted;

retain ct;

if first. light_no. then do; ct+1; output

end;

if last.ON_OFF_STATUS then do;

ct = .;

end;

run;

[Urban_Science]

I think you should be retaining the previous state of the switch and then counting when the previous state is OFF and the current state is ON.  

[Cynthia_sas]

Hi,

I'm not clear on your logic. You say that the light is only ON 2 times, but just counting, I see this:

where ON is the value for hour 3, 4 and 6. Is there a reason why you're not counting hour 6?

I would tend to use PROC TABULATE or PROC FREQ, if all you need is a report.

Cynthia

[novinosrin]

@YW_CA   In your sample it's been ON 3 times and not 2 times right?

If yes,

data have;
input Light_no   HOUR ON_OFF_STATUS $;
cards;
1                  1         OFF
1                  2         OFF
1                  3         ON
1                  4         ON
1                  5        OFF
1                  6        ON 
;

proc freq data=have noprint;
by Light_no;
where ON_OFF_STATUS='ON';
tables ON_OFF_STATUS/out=want;
run;

[Ksharp]

data have;
input Light_no   HOUR ON_OFF_STATUS $;
cards;
1                  1         OFF
1                  2         OFF
1                  3         ON
1                  4         ON
1                  5        OFF
1                  6        ON 
;
data _null_;
 set have;
 by  Light_no ON_OFF_STATUS notsorted;
 if first.Light_no  then count=0;
 count+first.ON_OFF_STATUS;
 if last.Light_no then do;
  _count=count-1;
  put 'Light_no ' Light_no  ' has ' _count ' switch';
 end;
run;

[s_lassen]

To calculate the number of times each light has been switched on, you can do something like this:

data want;
  n_switch_on=0;
  do until(last.light_no);
    set have;
    by Light_no on_off_status notsorted;
    n_switch_on+first.on_off_status and on_off_status='ON';
  end;
  keep light_no n_switch_on;
run;

Unless, of course, you do not want to count if the light is on in the first hour, then the last line in the loop should be

    n_switch_on+first.on_off_status and not first.light_no and on_off_status='ON';