topic Re: by process to get transition count in SAS Procedures

by process to get transition count

YW_CA — Fri, 08 Jun 2018 21:49:39 GMT

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;

Re: by process to get transition count

Urban_Science — Fri, 08 Jun 2018 22:28:41 GMT

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.

Re: by process to get transition count

novinosrin — Sat, 09 Jun 2018 00:48:39 GMT

@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;

Re: by process to get transition count

Cynthia_sas — Sat, 09 Jun 2018 00:52:36 GMT

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

Re: by process to get transition count

Ksharp — Sat, 09 Jun 2018 10:11:17 GMT

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;

Re: by process to get transition count

s_lassen — Mon, 11 Jun 2018 10:03:31 GMT

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';