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 = .;
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.
@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;
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;
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';
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.