CODEWITHSUNDEEP
sas
Betsy B
Betsy B

Reputation: 77

How can I select the first and last week of each month in SAS?

I have monthly data with several observations per day. I have day, month and year variables. How can I retain data from only the first and the last 5 days of each month? I have only weekdays in my data so the first and last five days of the month changes from month to month, ie for Jan 2008 the first five days can be 2nd, 3rd, 4th, 7th and 8th of the month. Below is an example of the data file. I wasn't sure how to share this so I just copied some lines below. This is from Jan 2, 2008. Would a variation of first.variable and last.variable work? How can I retain observations from the first 5 days and last 5 days of each month? Thanks.

1 AA  500 B 36.9800 NH 2 1 2008 9:10:21 
2 AA  500 S 36.4500 NN 2 1 2008 9:30:41
3 AA  100 B 36.4700 NH 2 1 2008 9:30:43 
4 AA  100 B 36.4700 NH 2 1 2008 9:30:48 
5 AA  50  S 36.4500 NN 2 1 2008 9:30:49

Upvotes: 1

Views: 2917

Answers (4)

Tom
Tom

Reputation: 51566

If you want to examine the data and determine the minimum 5 and maximum 5 values then you can use PROC SUMMARY. You could then merge the result back with the data to select the records.

So if your data has variables YEAR, MONTH and DAY you can make a new data set that has the top and bottom five days per month using simple steps. 

proc sort data=HAVE (keep=year month day) nodupkey 
     out=ALLDAYS;
   by year month day;
run;
proc summary data=ALLDAYS nway;
 class year month;
 output out=MIDDLE
   idgroup(min(day) out[5](day)=min_day)
   idgroup(max(day) out[5](day)=max_day)
  / autoname ;
run;
proc transpose data=MIDDLE out=DAYS (rename=(col1=day));
  by year month;
  var min_day: max_day: ;
run;
proc sql ;
  create table WANT as 
    select a.* 
    from HAVE a 
    inner join DAYS b
    on a.year=b.year and a.month=b.month and a.day = b.day
  ;
quit;

Upvotes: 2

Tom
Tom

Reputation: 51566

Use the INTNX() function. You can use INTNX('month',...) to find the beginning and ending days of the month and then use INTNX('weekday',...) to find the first 5 week days and last five week days.

You can convert your month, day, year values into a date using the MDY() function. Let's assume that you do that and create a variable called TODAY. Then to test if it is within the first 5 weekdays of last 5 weekdays of the month you could do something like this:

first5 = intnx('weekday',intnx('month',today,0,'B'),0) <= today
      <= intnx('weekday',intnx('month',today,0,'B'),4) ;
last5 = intnx('weekday',intnx('month',today,0,'E'),-4) <= today 
     <= intnx('weekday',intnx('month',today,0,'E'),0) ;

Note that those ranges will include the week-ends, but it shouldn't matter if your data doesn't have those dates. But you might have issues if your data skips holidays.

Upvotes: 1

Dirk Horsten
Dirk Horsten

Reputation: 3845

Create some data with the desired structure;

Data inData (drop=_:); * froget all variables starting with an underscore*;
    format date yymmdd10. time time8.;

    _instant = datetime(); 
    do _i = 1 to 1E5;
        date = datepart(_instant);
        time = timepart(_instant);
        yy = year(date);
        mm = month(date);
        dd = day(date);         

        *just some more random data*;
        letter = byte(rank('a') +floor(rand('uniform', 0, 26)));

        *select week days*;
        if weekday(date) in (2,3,4,5,6) then output;

        _instant = _instant + 1E5*rand('exponential');
    end;
run;

Count the days per month;

proc sql;
    create view dayCounts as
    select yy, mm, count(distinct dd) as _countInMonth
    from inData
    group by yy, mm;
quit;

Select the days;

data first_5(drop=_:) last_5(drop=_:);
    merge inData dayCounts;
    by yy mm;
    _newDay = dif(date) ne 0;

    retain _nrInMonth;
    if first.mm then _nrInMonth = 1;
    else if _newDay then _nrInMonth + 1;

    if _nrInMonth le 5 then output first_5;
    if _nrInMonth gt _countInMonth - 5 then output last_5;
run;

Upvotes: 1

user6542493
user6542493

Reputation: 59

/****
get some dates to play with
****/
data dates(keep=i thisdate);
offset = input('01Jan2015',DATE9.);
do i=1 to 100;
    thisdate = offset + round(599*ranuni(1)+1); *** within 600 days from offset;
    output;
end;
format thisdate date9.;
run;

/**** 
BTW: intnx('month',thisdate,1)-1 = first day of next month. Deduct 1 to get the last day
                                  of the current month.
     intnx('month',thisdate,0,"BEGINNING") = first day of the current month
****/
proc sql;
create table first5_last5 AS
SELECT
    *
FROM 
    dates /* replace with name of your data set */
WHERE
    /* replace all occurences of 'thisdate' with name of your date variable */
   ( intnx('month',thisdate,1)-5 <= thisdate <= intnx('month',thisdate,1)-1 )
   OR
   ( intnx('month',thisdate,0,"BEGINNING") <= thisdate <= intnx('month',thisdate,0,"BEGINNING")+4 )
ORDER BY
    thisdate;
quit;

Upvotes: 1

Related Questions