How to count a specific variable between a range in SAS using proc sql?-CodePudding

I'm new to SAS and I'm having problems with proc sql. I have a vertical dataset with:

ID    Code  Time
001   1     0:00:00.00
001   1     0:10:00.00
001   2     0:20:00.00
...   ...   ...
001   9     23:50:00.00

And I'm interested in having a table that summarizes how many N code 1, N code 2 and so on are between 0:00:00.00 and 23:50:00.00 for each ID. So, the output looks something like this:

ID   Code  N
001  1     28
001  2     17
001  3     5
...

Right now, I have something like this:

proc sql;
select Code,ID
from have
where Time between 0:00:00.000 and 23:50:00.000;quit;

If someone has an easier way and it's not with proc sql that's alright too. Thank you very much!

CodePudding user response：

To filter the data used in an analysis use a WHERE statement (or WHERE clause of an SQL statement). Make sure to use values that match the type of values in your variable.

where time between '00:00:00't and '23:00:00't

To count the number of observations you could use PROC SQL and the COUNT() aggregate function with the GROUP BY clause.

proc sql;
select Code,ID,count(*) as N
  from have
  where Time between '00:00:00't and '23:00:00't
  group by code, id
;
quit;

Or just use regular SAS code to do the counting instead.

proc summary data=have nway;
  where Time between '00:00:00't and '23:00:00't;
  class code id;
  output out=want(rename=(_freq_=N));
run;

If your TIME variable is actually character then trying to restrict the range will be hard if some of your strings have only one digit for the HOUR number. So convert it to a time value (number of seconds since midnight) to do the range testing.

  where input(Time,time12.) between '00:00:00't and '23:00:00't;