Percentage of time for a numerical variable to assume a value below a given threshold

[AlexanderL]

Dear all,

I have a dataset as shown below. ID is the subject ID and each of the variables Var1 and Var2 are measured every minute and can both have missing values.

Dataset HAVE: 

ID Minutes Var1 Var2
A 1 -3.2 -5.1
A 2 . -3.6
A 3 . -1.1
A 4 5.2 -4.9
B 1 3.2 1.2
B 2 4.5 1.2
B 3 3.1 4.2
C 1 . 1.9
C 2 . -2.2
C 3 2.1 -3.1

I would like to count the number of times each ID has a value of Var1 and Var2 below (e.g.) zero. This I would like to output as a new dataset showing for each ID, the period of time (one entry is one minute) each of the two variables had a value below zero, and the percentage/ratio of values below zero to the total number of entries (excluding missing values), e.g. like:

Dataset WANT:

ID Var1_minutes_below Var2_minutes_below Var1_perc_below Var2_perc_below
A 2 4 0.5 1
B 0 0 0 0
C 0 2 0 0.66

So far, I've been using SAS only for simple data management/statistics and this task I can't solve. Have tried reading a lot of posts solving similar problems using SQL/arrays, but I only have experience in the data step. I hope someone can help me with this?

Kind regards,
Alexander

[ballardw · Accepted solution]

One way:

Please note the data step as the way to provide data that we can test with.

data have;
  input ID $ Minutes Var1 Var2;
  if not missing(var1) then var1_below=  (. < var1 <0)  ;
  if not missing(var2) then    var2_below=  (. < var2 <0)  ;
datalines;
A 1 -3.2 -5.1
A 2 . -3.6
A 3 . -1.1
A 4 5.2 -4.9
B 1 3.2 1.2
B 2 4.5 1.2
B 3 3.1 4.2
C 1 . 1.9
C 2 . -2.2
C 3 2.1 -3.1
;
run;

proc summary data=have nway;
   class id;
   var var1_below var2_below;
   output out=havesum (drop= _:) sum= n= /autoname;
run;

data want;
   set havesum;
   var1_perc_below = var1_below_sum/ var1_below_n;
   var2_perc_below = var2_below_sum/ var2_below_n;
   rename var1_below_sum=var1_below_minutes  
          var2_below_sum=var2_below_minutes 
   ;
   drop var1_below_n var2_below_n;
run;

to calculate a percent you need to have a denominator. I am inferring that you want the number of records.

I am creating separate _N variables as you may have data at some point where you have values that you may not want to include for the denominator so getting in the habit of identifying each N is good idea.

Note if you want a report you could use a different approach with a report procedure

proc tabulate data= have;
   class id;
   var var1_below var2_below;
   table id='',
         (var1_below="Var1 minutes Below" var2_below="Var2 minutes Below")*sum=""*f=best5.
         (var1_below="Var1 % minutes Below" var2_below="Var2 % minutes Below")*mean=""*f=best5.
         /box=id
   ;
run;

[soham_sas]

Hi , assuming the missing the value as zero (which is not less than zero but equal to zero) and some of the var1 and var2 are negative (which is less than zero)

please try like this

data have;
input ID$ Minutes Var1 Var2;
cards;
A 1 -3.2 -5.1
A 2 . -3.6
A 3 . -1.1
A 4 5.2 -4.9
B 1 3.2 1.2
B 2 4.5 1.2
B 3 3.1 4.2
C 1 . 1.9
C 2 . -2.2
C 3 2.1 -3.1
;
run;

data have1;
set have;
if var1 < 0 then Var1_minutes_below = var1;
if var2 <0 then Var2_minutes_below=var2;
run;

proc sql;
create table have2 as select id , max(Minutes) as minutes from have 
group by id;
quit;

proc sql;
create table have3 as select id , count(Var1_minutes_below) as Var1_minutes_below,
count(Var2_minutes_below) as Var2_minutes_below from have1
group by 1;
quit;

proc sql;
create table want as 
select a.*, a.Var1_minutes_below/b.minutes as Var1_perc_below , 
a.Var2_minutes_below/b.minutes as Var2_perc_below
from have3 as a left join have2 as b
on a.id = b.id;
quit;

[AlexanderL]

Thank you so much for your reply! This solution works absolutely fine! As I'm not used to working with proc sql, I think I will start with the solution posted above using only the data step, which I'm more familiar with. 

Regards,

Alexander