06-08-2018 05:49 PM
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?
where ON_OFF_STATUS = ON;
by light_no. ON_OFF_STATUS notsorted;
if first. light_no. then do; ct+1; output
if last.ON_OFF_STATUS then do;
ct = .;
06-08-2018 08:52 PM
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.
06-08-2018 08:42 PM - edited 06-08-2018 08:48 PM
@YW_CA In your sample it's been ON 3 times and not 2 times right?
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;
06-09-2018 06:11 AM
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;
06-11-2018 06:03 AM
To calculate the number of times each light has been switched on, you can do something like this:
by Light_no on_off_status notsorted;
n_switch_on+first.on_off_status and on_off_status='ON';
keep light_no n_switch_on;
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';