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
num | StartDate | EndDate | select |
---|---|---|---|
001 | 25/09/2013 | 4/10/2013 | 1 |
0002 | 26/09/2013 | 28/09/2013 | 0 |
0003 | 1/02/2012 | 05/04/2012 | 0 |
0004 | 30/09/2013 | 1/10/2013 | 1 |
0005 | 3/10/2013 | 8/10/2013 | 1 |
0006 | 26/10/2013 | 15/11/2013 | 1 |
0008 | 26/11/2013 | 4/12/2013 | 0 |
0009 | 30/09/2013 | 21/11/2013 | 1 |
0010 | 8/09/2013 | 4/11/2013 | 1 |
0011 | 3/05/2013 | 13/11/2013 | 1 |
0012 | 3/06/2013 | 25/07/2013 | 0 |
0013 | 2/09/2012 | 25/11/2012 | 0 |
0014 | 2/05/2014 | 21/05/2014 | 0 |
0015 | 17/09/2013 | 7/11/2013 | 1 |
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
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;
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...
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).
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
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
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
I'm in date format 25sep2013 is 19626
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
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
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.