I need the following code to run on the sample dataset attached. The goal is to select all values of orig_date between today and six months ago.
Any ideas? Thanks in advance.
data test;
set farm_final;
end_date=%eval(&sysdate-180);
where orig_date between &sysdate and end_date;
run;
How about something like this?
%let today_back_six = %sysfunc(putn(%sysfunc(intnx(MONTH, "&sysdate"d, -6, SAME)), date9.));
%put &today_back_six;
where orig_date between "&sysdate"d and "&today_back_six"d;
I should specify; orig_date is a character variable.
orig_date appears as MM\YYYY.
When converting this to a date do you want the first day of the month or the last day of the month?
How about this?
DATA HAVE;
INFILE DATALINES;
INPUT ORIG_DATE $;
DATALINES;
01/2013
02/2014
04/2012
06/2014
09/2009
12/2008
03/2014
;
RUN;
%LET SIXMTH = %SYSFUNC(PUTN(%SYSFUNC(INTNX(MONTH,%SYSFUNC(DATE()), -6, SAME)), DATE9.));
DATA WANT (WHERE = (ORIGDATE BETWEEN DATE() AND "&SIXMTH"D));
SET HAVE;
ORGMTH = INPUT(SUBSTR(ORIG_DATE,1,INDEX(ORIG_DATE,'/')-1),BEST2.);
ORGYR = INPUT(SUBSTR(ORIG_DATE,INDEX(ORIG_DATE,'/')+1),BEST4.);
ORIGDATE = MDY(ORGMTH,1,ORGYR);
FORMAT ORIGDATE DATE9.;
RUN;
Thanks to both. When I run the following, i get the error "ERROR: Where clause operator requires compatible variables." Why is that and what can I do? The same occurs when I run the other code listed.
%let today_back_six = %sysfunc(putn(%sysfunc(intnx(MONTH, "&sysdate"d, -6, SAME)), date9.));
%put &today_back_six;
where orig_date between "&sysdate"d and "&today_back_six"d;
This is because orgin_date is a character variable.
Well, without all the macro code:
data have;
attrib orig_date format=$7.;
input orig_date $;
datalines;
01/2013
02/2014
04/2012
06/2014
09/2009
12/2008
03/2014
;
run;
data want;
set have;
if intnx('month',today(),-6,'SAME') <=
input("01"||substr(orig_date,1,2)||substr(orig_date,4,4),ddmmyy8.) <=
today() then output;
run;
data want;
set have;
where intnx('day',input(orig_date,anydtdte7.),0,'b') between "&sysdate"d and "&today_back_six"d;
run;
How about something like this?
%let today_back_six = %sysfunc(putn(%sysfunc(intnx(MONTH, "&sysdate"d, -6, SAME)), date9.));
%put &today_back_six;
where orig_date between "&sysdate"d and "&today_back_six"d;
end_date=%sysevalf( "&sysdate"d -180);
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!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.