Hello,
I am trying to filter dates in proc sql using macro variables. I could use some help getting it to work.
data _null_;
MP4DB= intnx ('month',today(),-4,'B');
MP2DE= intnx ('month',today(),-2,'E');
call symput ('MP4DB', MP4DB);
Call Symput ('MP2DE', MP2DE);
Call symput ('MP4DB_DDMMYYS8', cats(put(MP4DB, ddmmyys8.)));
Call symput ('MP2DE_DDMMYYS8', cats(put(MP2DE, ddmmyys8.)));
run;
%put _user_;
PROC SQL;
CREATE TABLE WORK.total_test AS
SELECT DISTINCT t1.MEMID,
t1.MBR_LAST,
t1.MBR_FIRST,
/* SUM_of_ALLOW */
(SUM(t1.ALLOW)) AS SUM_of_ALLOW,
/* SUM_of_PAID */
(SUM(t1.PAID)) AS SUM_of_PAID
FROM WORK.COST_BY_MEMBERS t1
WHERE t1.ADMITDT BETWEEN ___________________________
GROUP BY t1.MEMID
ORDER BY SUM_of_PAID DESC;
QUIT;
How can i use my begining and end dates from the macro variables in the where clause? I have tried several ways and cant get it to work. Any resources would be appreciated.
Thank you.
Hi mate,
I think it is simply you add a max and min function into your SQL and use then in the having clause.
data test;
format date date9.;
input date :date9.;
cards;
01jan2015
02jan2015
30jan2015
;
run;
proc sql;
select date, max(date) as max_date format=date9.,
min(date) as min format=date9. from test
having date between min(date) and max(date);
quit;
Try this. Having clause can use summary functions.
Hope this helps.
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.