BookmarkSubscribeRSS Feed
beresh
Calcite | Level 5

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


10 REPLIES 10
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

beresh
Calcite | Level 5

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...

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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).

beresh
Calcite | Level 5

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

TomKari
Onyx | Level 15

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

beresh
Calcite | Level 5

I'm in date format 25sep2013 is 19626

beresh
Calcite | Level 5

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

TomKari
Onyx | Level 15

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

cov_derek
Fluorite | Level 6

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 1590 views
  • 6 likes
  • 4 in conversation