BookmarkSubscribeRSS Feed
YW_CA
Calcite | Level 5

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;

 

5 REPLIES 5
Urban_Science
Quartz | Level 8

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
Diamond | Level 26

Hi,

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

on_off.png

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
Tourmaline | Level 20

@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
Super User
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
Meteorite | Level 14

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

hackathon24-white-horiz.png

The 2025 SAS Hackathon Kicks Off on June 11!

Watch the live Hackathon Kickoff to get all the essential information about the SAS Hackathon—including how to join, how to participate, and expert tips for success.

YouTube LinkedIn

What is Bayesian Analysis?

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • 1779 views
  • 1 like
  • 6 in conversation