07-31-2014 05:00 AM
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
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!
using SAS EG 4.1
07-31-2014 05:06 AM
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:
07-31-2014 05:37 AM
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...
07-31-2014 05:45 AM
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:
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).
07-31-2014 05:58 AM
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?
07-31-2014 06:30 AM
Well, there is this:
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;
if start_date <= "31OCT2013"d and end_date <= "01OCT2013"d then select=1;
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
07-31-2014 07:10 AM
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.
07-31-2014 10:17 AM
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
07-31-2014 05:03 PM
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.
07-31-2014 05:12 PM
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.