CODEWITHSUNDEEP
databasesas
Ckeane
Ckeane

Reputation: 167

SAS Getting all the information from last month (01 - 28/29, 30, 31)?

I am trying to pull data from a table to cover the last calendar month. The report can be pulled on any day of the following month. I am using the following code:

CURRENT DATE - 31 DAYS - DAYOFWEEK_ISO(CURRENT DATE) DAYS)

However, it pulls in information from the current month etc... Is there a a variable or something to ensure that just the last monts data is returned?

Thanks.

Here is the way one of my work mates did it:

CURRENT DATE - DAY(CURRENT DATE) DAYS + 1 DAY - 1 MONTH 
AND CURRENT DATE - DAY(CURRENT DATE) DAYS

Upvotes: 1

Views: 4224

Answers (2)

Jay Corbett
Jay Corbett

Reputation: 28441

The INTNX function is great for dates. Here are two examples one subsetting using a data step and one using Proc SQL.

data dates(drop=i);
 input date:DATE9. @;
 do i = 1 to 4;
  input name $ weight @;
  output;
 end;
 format date mmddyy10.;
 datalines;
 05FEB2012 Barbara 125 Alice 130 Ronald 170 John 160
 04FEB2012 Barbara 122 Alice 133 Ronald 168 John 155
 15FEB2012 Barbara 135 Alice 140 Ronald 190 John 180
 25FEB2012 Barbara 142 Alice 113 Ronald 178 John 185
 29FEB2012 Barbara 185 Alice 170 Ronald 160 John 150
 04MAR2012 Barbara 192 Alice 183 Ronald 178 John 165
;

Data _null_;
 call symputx('beg_dt',intnx('month',today(),-1,'b'));
 call symputx('end_dt',intnx('month',today(),-1,'e'));
run;

Data subset;
 set dates(where=(&beg_dt <= date <= &end_dt));
run;

PROC SQL noprint;
 CREATE TABLE subset_sql AS
 SELECT *
 FROM dates
 WHERE &beg_dt <= date <= &end_dt;
QUIT;

INTNX generates a date value. The interval can be many things other than MONTH (ex YEAR, WEEK). You can even use it to determine fiscal year with YEAR.N (where N is the month your fiscal year starts).

Data _null_;
 Current_FY=year(intnx('year.9',today(),0,'e'));
 put current_fy=;
run;

The last (optional) argument is for alignment (Beginning, Middle, End or Same).

Upvotes: 5

DavB
DavB

Reputation: 1696

You could create macro variables for the first and last day of last month:

data _null_;
  latest=input("01"||substr(put(today(),date9.),3),date9.)-1; /* Last day of last month */
  earliest=input("01"||substr(put(latest,date9.),3.),date9.); /* First day of last month */
  call symput('mlatest',put(latest,date9.));
  call symput('mearliest',put(earliest,date9.));
run;
%put First day of last month: &mearliest;
%put Last day of last month:  &mlatest;

Then use these macro variables when reading in your data set:

data last_month;
  set all_data(where=("&mearliest"d<=date_var<="&mlatest"d));
run;

Upvotes: 1

Related Questions