Desktop productivity for business analysts and programmers

how to select the attendance for a month?

Reply
Occasional Contributor
Posts: 5

how to select the attendance for a month?

Hi,

I would like to select all observations who are present one month in a year with SAS EG.

I have Start date et end Date as data with format Date and I'm running out of ideas

numStartDateEndDateselect
00125/09/20134/10/20131
000226/09/201328/09/20130

0003

1/02/201205/04/20120
000430/09/20131/10/20131
00053/10/20138/10/20131
000626/10/201315/11/20131
000826/11/20134/12/20130
000930/09/201321/11/20131
00108/09/20134/11/20131
00113/05/201313/11/20131
00123/06/201325/07/20130
00132/09/201225/11/20120
00142/05/201421/05/20140
001517/09/20137/11/20131

I've tried for example a filter or a calculated column: DATEPART(StartDate)<=MDY(10,31,2013) AND DATEPART(EndDate) >= MDY(10,1,2013) for October 2013

But it does not work. The query returns empty.


Is that someone would help me? I am a novice...

Thanks in advance!


Beresh

using SAS EG 4.1


Super User
Super User
Posts: 7,682

Re: how to select the attendance for a month?

Hi,

Well, don't know EG too well, but base SAS has functions for this.  For example MONTH(date) will return 1-12 depending on the month in the date.  So if I want all observations in Febuary then:
data want;

     set have;

     where month(my_date)=2;

run;

Occasional Contributor
Posts: 5

Re: how to select the attendance for a month?

Thank you for your help.

If I do this, I'll just have those who started in February or finished in February but not those who are there in February but started earlier in January for example...

Super User
Super User
Posts: 7,682

Re: how to select the attendance for a month?

Ah, that's a whole other question.  Firstly, what defines your month in this instance, as someone who starts in Jan into early Feb has less days than Feb into March.

What you could do is something like:

if intck('month',start_date,end_date)=1 then ...

You will need to check the SAS help on this as:

The function INTCK('MONTH','1feb1991'd,'31jan1991'd) returns –1 because the first date is in a later discrete interval than the second date. (INTCK returns a negative value whenever the first date is later than the second date and the two dates are not in the same discrete interval.)

It comes down to how you define a month really as its not a fixed time (like say weeks are).

Occasional Contributor
Posts: 5

Re: how to select the attendance for a month?

By example if I'm in a hospital: patients come in and patients come out but have sometimes a long stay.

Here I would like to know how many inpatient I've got during the past month?

Not the new in but all inpatients regardless of the length of the month.


Did I answer your question?

Thanks

Super User
Super User
Posts: 7,682

Re: how to select the attendance for a month?

Well, there is this:

data have;
  attrib id format=best. start_date end_date format=date9.;
  id=1; start_date="25SEP2013"d; end_date="04OCT2013"d; output;
  id=2; start_date="26SEP2013"d; end_date="28SEP2013"d; output;
run;

data want;
  set have;
  if start_date <= "31OCT2013"d and end_date <= "01OCT2013"d then select=1;
run;

However I feel your logic is not complete.  A subject with 01Jan2013 and end date 02Jan2013 will fulfill your criteria whereas the subject you have marked in your first example id=1 would not as end date of 04oct2013 is not less than or equal to 01oct13.

I think you would need to expand your logic to cover:

start_date <= month_start and end_date > month_start

start_date > month_start and start_date <= month_end and end_date > month_start

PROC Star
Posts: 1,143

Re: how to select the attendance for a month?

I tried your logic with a little different coding, and it works fine.

First of all, are you sure that your dates are in the SAS datetime format? SAS uses two ways of representing date data; as a number of days since 1jan1960 (date) and as the number of seconds since the same date (weird, I know, but you come to appreciate it.) In date format, 25sep2013 is 19626, in datetime midnight is 1695686400. Check what your numeric values are.

Post back when you know more.

Tom

Occasional Contributor
Posts: 5

Re: how to select the attendance for a month?

I'm in date format 25sep2013 is 19626

Occasional Contributor
Posts: 5

Re: how to select the attendance for a month?

I think I managed:

I've turned my date into a number like: 25sep2013 = 19626

If it's for February:

CASE WHEN (numberStartDate <= 19417) AND (numberEndDate >= 19390) THEN 1

          ELSE 0 END

Thanks to all

PROC Star
Posts: 1,143

Re: how to select the attendance for a month?

Way to go!

The reason I was asking whether you were using date or datetime values is that you can very conveniently use SAS date and datetime literals. Your expression should also work as

CASE WHEN (numberStartDate <= '28feb2013'd) AND (numberEndDate >= '1feb2013'd) THEN 1

          ELSE 0 END

Note the d after the quote. For a datetime it would be '28feb2013:0:0:0'dt.

Tom

Contributor
Posts: 23

Re: how to select the attendance for a month?

I'm still not sure what you're wanting here. However, the easiest way to dynamically calculate the start and end of a CALENDAR month is with the INTNX() function. INTNX('MONTH',date,0,'b') will give you the first day for the month of the variable date as a SAS date value. There are other ways to do this, and they work because the first day in a calendar month is always 1. End of calendar months is trickier, and this is where the INTNX function is useful: INTNX('MONTH',date,0,'e'), provides the last day for the month of variable date as a SAS date value, which is what you are using in your CASE statements.

Please note the quotation marks, they are required. MONTH is the SAS interval name, and 'b' and 'e' are called "alignment operators" and they signify the beginning and the end of the interval.

Ask a Question
Discussion stats
  • 10 replies
  • 539 views
  • 6 likes
  • 4 in conversation