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);
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.