topic Re: only keep last 12 months of data sas in SAS Programming

only keep last 12 months of data sas

sasprogramming — Wed, 21 Jul 2021 14:02:12 GMT

I have a dataset in sas which contains many columns. One of which is a date column called `LODGE_DATE`.

The dataset contains about 3years worth of data and will contiue to grow. I only want to export the last 12months worth of data, how can I do this without choosing the actual months to include/exclude?

'LODGE_DATE' is formatted MONYY5.

Thanks

Re: only keep last 12 months of data sas

ballardw — Wed, 21 Jul 2021 14:09:04 GMT

If your variable is an actual SAS date, not a character value or simple number that looks like JUL21 (BTW 2 digit years are a bad idea), then anything you want to use a subset based on date is a WHERE statement or data set option

Data want;

set have;

where lodge_date ge '01JAN2019'd;

run;

for example selects all observations where the date in lodge_date is 01JAN2019 or later. The date literal to compare with must be in the Date9 (or Date7) appearance with the quotes and the d. The d tells SAS you want the date value represented by the string.

If this doesn't work the show us Proc Contents output describing your data.

Re: only keep last 12 months of data sas

sasprogramming — Wed, 21 Jul 2021 14:13:28 GMT

Thanks, this works but I want it to be the last 12months regardless of what date is the last observation.

Like the logic i am after is:

keep rows for: (last.date - 12months)

Re: only keep last 12 months of data sas

sasprogramming — Wed, 21 Jul 2021 14:56:52 GMT

'LODGE_DATE' is now formatted yymmd7.

Re: only keep last 12 months of data sas

mkeintz — Wed, 21 Jul 2021 14:59:11 GMT

If the most recent date in your dataset is the last record, then you can read that record, calculate the date 1 year before, and then use that date in a WHERE statement:

data _null_;
  set have point=lobs nobs=lobs;
  cut_point=intnx('year',lodge_date,-1,'S');
  call symput("cut_point",put(cut_point,date9.));
  stop;
run;
%put &=cut_point;

data want;
  set have;
  where lodge_date>="&cut_point"d;
run;

The first DATA step reads exactly one record - the last one. The cut_point is calculated as one year earlier, and the date is written to a macrovar CUT_PONT. The CUT_POINT text (yes, it's text, not numeric once it is a macrovar), is in date9 format (see the log for the %PUT statement).

Then, in the second DATA step, that macrovar fits nicely in the

where lodge_date>="&cut_point"d ;

filter.

Re: only keep last 12 months of data sas

ballardw — Wed, 21 Jul 2021 15:57:53 GMT

@sasprogramming wrote:

'LODGE_DATE' is now formatted yymmd7.

FORMATS have no impact on comparison, calculation or other manipulation (unless the code explicitly uses a format to create at text value).

Re: only keep last 12 months of data sas

Kurt_Bremser — Wed, 21 Jul 2021 20:31:20 GMT

And if the "last date" is buried in an arbitrary observation, do this:

proc sql noprint;
select intnx('year',max(lodge_date),-1,'s') into :cutoff from have;
quit;

data want;
set have;
where lodge_date ge &cutoff.;
run;