How to find the usage within two dates

[Hiandbye]

Hello!

Can you please help me find how to filter a data set that contains two variables: Model _ID and Date_Received. What I need is to find the number of runs of the models within last 7 days, 30days ,1 year. 

So if we ran it today, it would count usage between 03Jan2020 - 10Jan2020 inclusive ,

if we run it tomorrow, it would count usage between 04Jan2020 - 11Jan2020 inclusive etc. The output data should be a table that has the models and the number of runs only.

I have chosen to do a proc freq step but don't know how filter the dates, any help is much appreciated!

[Shmuel]

To select models received in a period use where clause:

where <date> between <date1> and <date2>; 

or more specifically as in your case:

where last_run between today()-6 and today();

You can use it in a data step to extract the data:

data part;
 set have(where=(last_run in ...));
   ...
run;

or in a procedure - proc freq to count how many models run on each day:

proc frec data=have(where=(last_run between ...));;
   table lasr_run;
   format last_run <any date format ended with a dot>;
run;

[Hiandbye]

Thank you for your answer but I get a syntax error when I tried it. 

proc freq data=MORIS.Test_notification_data  ;
tables Model_ID /nopercent ;
where  dt_rec between today()-6 and today();
RUN;

[Hiandbye]

I get this error message : No observations were selected from data set MORIS.TEST_NOTIFICATION_DATA.
NOTE: There were 0 observations read from the data set MORIS.TEST_NOTIFICATION_DATA.. Any thoughts why this could be ? Thanks

[Kurt_Bremser]

Please post the complete log from your step.

And get to know your data (types and formats of variables, retrieved with PROC CONTENTS), so you better know how to code the WHERE.

[Hiandbye]

Thanks ! Proc contents will be my ally from now on 🙂

[FreelanceReinh · Accepted solution]

Hello @Hiandbye,

---

@Hiandbye wrote:
I get this error message : No observations were selected from data set MORIS.TEST_NOTIFICATION_DATA.
NOTE: There were 0 observations read from the data set MORIS.TEST_NOTIFICATION_DATA.. Any thoughts why this could be ? Thanks

---

You need to use a condition which is appropriate for the contents of the variable in question and, of course, the correct variable name (i.e., "know your data"  -- Maxim 3). In your initial post you mentioned Date_Received as the variable name, in your code you used dt_rec and the "dates" in your screenshot are headed "Last_Run" and are in fact datetimes.

Example: If variable dt_rec contains SAS datetimes from the past and you want to select those which are up to 7 days old, more precisely: today (10 Jan 2021) those from 03JAN2021:00:00:00 or later, an appropriate WHERE statement for your PROC FREQ step is:

where dt_rec>=intnx('dtdays',datetime(),-7);

(See the documentation of the INTNX function for more details and options.)

[Hiandbye]

Good shout thanks! It worked 🙂

[Kurt_Bremser]

proc sql;
create table want as
  select
    model_id,
    count(*) as count
  from have
  group by model_id
  where today() - 7 le date_received le today()
;
quit;

Untested; for tested code, supply your dataset in a data step with datalines, and post the code in a window opened with the "little running man" button (8th in the photo).

[Hiandbye]

Thanks for your answer! Not sure if I can use proc sql as I was asked to use proc freq instead. I tried the code and it gives me an error syntax at the where clause.