WHERE syntax: How to appropriately use it in excluding mutiple variables needed for a data step

[oadeyemi]

Good evening everyone.

I am having problems with the WHERE syntax. I think I am using it inappropriately.

I have gone online but no satisfying solution

Background of the problem:

the variables not_hour, not_min, arr_hour, arr_min, hosp_hr, and hosp_mn are numbers that represent notification hour, notification minutes, arrival hour, arrival minute., hospital arrival hour and hospital arrival minute, respecctively.

The hour column is coded 0-24. 88 to 99 represents unknown

The minute column is coded 0-60. 88-99 represents unknown.

My plan is to 

1. concatenate the associated hour and minute columns (i.e.:not_hour & not_min = notification time in HH:MM

                                                                             ,                  arr_hour &  arr_min = arrival time in HH:MM

                                                                                               hosp_hr  & hosp_mn = hospital arrival time in HH:MM)

2. make them into a time format (in HH:MM)

3. generate time duration from them (e.g. hospital time - notification time)

So far, all worked well! 

But the WHERE syntax that I am using to exclude variables that have values above 0-24 in the hour column and 0-60 in the minute column is not responding.

I still have inappropriate values despite the WHERE syntax

Please can you help take a look at the code?

Thanks

data R11time;
set R11a;
cnot_hour= put(not_hour,z2.);
cnot_min= put(not_min,z2.);
not_tim= cat(cnot_hour,cnot_min);
   where (not_hour >=1 and not_hour <=24);
   where (not_min >=0 and not_min <=60);
nottime=input(not_tim,hhmmss4.);
  format nottime time5.;
  drop cnot_hour cnot_min not_tim;
carr_hour= put(arr_hour,z2.);
carr_min= put(arr_min,z2.);
arr_tim= cat(carr_hour,carr_min);
   where (arr_hour >=1 and arr_hour <=24);
   where (arr_min >=0 and arr_min <=60);
arrtime=input(arr_tim,hhmmss4.);
  format arrtime time5.;
  drop carr_hour carr_min arr_tim;
chosp_hr= put(hosp_hr,z2.);
chosp_mn= put(hosp_mn,z2.);
hosp_tim= cat(chosp_hr,chosp_mn);
  where (hosp_hr >=1 and hosp_hr <=24);
  where (hosp_mn >=0 and hosp_mn <=60);
hosptime=input(hosp_tim,hhmmss4.);
  format hosptime time5.;
  drop chosp_hr chosp_mn hosp_tim;
tan = (nottime - actime)/60;
tasa = (arrtime - actime)/60;
taha = (hosptime - actime)/60;
tnsa = (arrtime - nottime)/60;
tnha = (hosptime - nottime)/60;
tsaha = (hosptime - arrtime)/60;
run;

[Kurt_Bremser]

Only one where condition in a data step will be effective, namely the last one. Combine all your where conditions into one with and.

[oadeyemi · Accepted solution]

Wow!

Thanks for your quick response.

It worked.

I had tried writing all the WHERE's in one line but I was using an OR ..and it makes sense, OR will not pick all the columns.

And I noticed that after using 'and' more than once, the color changes from blue to the variable color. I guess that doen not matter.

A proc univariate showed that the ranges are within the expected values.

Thank you so much.

[Astounding]

There are a few basic concepts about a WHERE statement that you need to know.

First, when you use multiple WHERE statements, each one replaces the ones before.  So the last WHERE statement is the only one that has any impact. 

Second, WHERE screens out the entire observation from all processing.  So if HOSP_MIN = 80, that observation is excluded from all the calculations, including the calculations for NOTTIME and ARRTIME.  Is that your intention?

It is conceivable that IF THEN statements would be more suitable.  For example you now have:

hosp_tim= cat(chosp_hr,chosp_mn);
  where (hosp_hr >=1 and hosp_hr <=24);
  where (hosp_mn >=0 and hosp_mn <=60);
hosptime=input(hosp_tim,hhmmss4.);

It might be that you would prefer to use:

if (hosp_hr >=1 and hosp_hr <=24)
and (hosp_mn >=0 and hosp_mn <=60)
then do;
   hosp_tim= cat(chosp_hr,chosp_mn);
   hosptime=input(hosp_tim,hhmmss4.);
end;

[oadeyemi]

Thank you for your comment. The if statement also works. But I will prefer excluding the missing variables entirely across board. Thank you so much for the explanation.