BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
BC33
Fluorite | Level 6

I have a dataset where the first column is a datetime, and the other columns contain integers.  Each row represents one second with corresponding values.  

 

How would I find out how many times a value in the integer column was under 50 and then went over 50 for a minimum of n seconds?

 

I realize this may be a complex solution, so please let me know if you need more info!

 

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

I would add variables to your data set, to give you flexibility in the later analysis.  It's a bit of a headache, but here is the idea.  Let's assume one of your existing variables is named ABC:

 

data want;

set have;

prior_abc = lag(abc);

if _n_=1 and abc < 50 then abc_group=1;

if (abc >= 50 and prior_abc < 50) or (abc < 50 and prior_abc >= 50) then do;

   abc_group + 1;

   n_seconds_abc = 1;

end;

else n_seconds_abc + 1;

drop prior_abc;

run;

 

Note that I included 50 exactly as part of the 50+ group. 

 

You should be able to run this on your data and get an idea of what the new variables look like.  I tested at least some variations of the code.  Note that the number of seconds in the first grouping may be off, but that won't matter since the first grouping is never used for analysis.

 

Then consider an analysis question:

 

For ABC, how many times was the value under 50, then rose to 50+?  How long did the 50+ measurement last at least N seconds?

 

proc freq data=want;

where abc_group > 1 and abc >= 50;

tables n_seconds_abc;

run;

 

Test it with a relatively small amount of data to get a feel for what the new variables and the tabular results represent.

 

 

View solution in original post

11 REPLIES 11
Astounding
PROC Star

I would add variables to your data set, to give you flexibility in the later analysis.  It's a bit of a headache, but here is the idea.  Let's assume one of your existing variables is named ABC:

 

data want;

set have;

prior_abc = lag(abc);

if _n_=1 and abc < 50 then abc_group=1;

if (abc >= 50 and prior_abc < 50) or (abc < 50 and prior_abc >= 50) then do;

   abc_group + 1;

   n_seconds_abc = 1;

end;

else n_seconds_abc + 1;

drop prior_abc;

run;

 

Note that I included 50 exactly as part of the 50+ group. 

 

You should be able to run this on your data and get an idea of what the new variables look like.  I tested at least some variations of the code.  Note that the number of seconds in the first grouping may be off, but that won't matter since the first grouping is never used for analysis.

 

Then consider an analysis question:

 

For ABC, how many times was the value under 50, then rose to 50+?  How long did the 50+ measurement last at least N seconds?

 

proc freq data=want;

where abc_group > 1 and abc >= 50;

tables n_seconds_abc;

run;

 

Test it with a relatively small amount of data to get a feel for what the new variables and the tabular results represent.

 

 

BC33
Fluorite | Level 6

I appreciate your response.  This is definitely getting me on the right track!  However, I am getting an error.  It is saying the new variables are "not on file" for the dataset.  I did use your code and switched abc for the real variable name.  

 

I imported the data from excel and it lives in a table.  Does this change anything?

 

Thanks!

ballardw
Super User

Post the actual log with error messages here using the code box opened with the forum {i} menu icon.

 

Importing from Excel may have a number of issues such as you thinking you know the name of the variable(column heading) but the imported result having additional characters such as _ from spaces or other invalid characters in the variable name or requiring use of "name with space"n syntax.

BC33
Fluorite | Level 6
982   DATA WORK;
983   set data_full;
984   prior_data = lag(_variable);
985   if _n_=1 and variable < 50 then variable_group=1;
986   if(variable >= 50 and prior_data < 50) or (variable < 50 and prior_data >= 50) then do;
987   variable_group + 1;
988   n_seconds_data = 1;
989   end;
990   else n_seconds_data + 1;
991   drop prior_data;
992   run;

NOTE: There were 1200 observations read from the data set WORK.DATA_FULL.
NOTE: The data set WORK.WORK has 1200 observations and 4 variables.
NOTE: DATA statement used (Total process time):
      real time           0.03 seconds
      cpu time            0.01 seconds


993
994   proc freq data=WORK.data_full;
995   where variable_group > 1 and variable >= 50;
ERROR: Variable variable_group is not on file WORK.DATA_FULL.
996   tables n_seconds_data;
ERROR: Variable N_SECONDS_DATA not found.
997   run;

Yes, when importing from Excel I have noticed changes in variable names; however, the issue looks like it is related to the variables being created in the first step you posted.  They are not being recognized.  Do I need to add these variables to the dataset separately?

 

Thanks! 

Rick_SAS
SAS Super FREQ

You've called the new data set "work" (probably not a good name)

982   DATA WORK;

so you need to use that name in PROC FREQ:
 

994   proc freq data=WORK;
BC33
Fluorite | Level 6

You are correct.  I am now getting the expected results.  Thanks for the tip!

BC33
Fluorite | Level 6

Thanks for your answer.  I realize this next solution may be similar in manner, but how would you best find how many events there are where x > 50 for 10 consecutive seconds (or rows)?  As you may have guessed, I am a beginner at SAS programming.  Your help is greatly appreciated!

 

Thanks!

 

ChrisNZ
Tourmaline | Level 20
BC33
Fluorite | Level 6

Can you please explain what this does?  I run your code and get a GROUP= output, but the output value doesn't quite seem to match what I am looking for, and I suspect it could be this code.   

VAL=ranuni(0)*200;

 Thanks!

BC33
Fluorite | Level 6

No worries.  I have refined the code to use values from the dataset instead of random numbers and your code works great.  Although the original question had a slightly different context, I still appreciate your solution.

 

Thanks!

ChrisNZ
Tourmaline | Level 20

Like this?


data HAVE;
  do DT=1 to 1000;
    VAL=ranuni(0)*200;
    output;
  end;
run;

%let nb_seconds=10;

data COUNT;
  set HAVE end=LASTOBS;
  if VAL > 50 then SEQ+1;
  else SEQ=0;
  if SEQ = &nb_seconds. then GROUP+1;
  if LASTOBS then putlog GROUP=;
run;

  

GROUP=10

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 11 replies
  • 782 views
  • 4 likes
  • 5 in conversation