DATA Step, Macro, Functions and more

Macro and proc sql help

Accepted Solution Solved
Reply
Occasional Contributor Jse
Occasional Contributor
Posts: 17
Accepted Solution

Macro and proc sql help

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.


Accepted Solutions
Solution
‎11-03-2015 02:47 PM
Super User
Posts: 17,960

Re: Macro and proc sql help

Remember that it has to resolve to valid SAS code. If the dates are SAS dates, numbers, then the following works. If you apply formats, then you need to make sure they resolve to appropriate date literals in SAS.

&MP4DB and &MP2DE

View solution in original post


All Replies
Solution
‎11-03-2015 02:47 PM
Super User
Posts: 17,960

Re: Macro and proc sql help

Remember that it has to resolve to valid SAS code. If the dates are SAS dates, numbers, then the following works. If you apply formats, then you need to make sure they resolve to appropriate date literals in SAS.

&MP4DB and &MP2DE
Occasional Contributor Jse
Occasional Contributor
Posts: 17

Re: Macro and proc sql help

Thank you Reeza, you are a tremendous help. Much appreciated.
Regular Contributor
Posts: 212

Re: Macro and proc sql help

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.

Super User
Posts: 17,960

Re: Macro and proc sql help

Also, please note that you could put those intnx functions directly into the WHERE clause instead of using macro variables.
🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 197 views
  • 0 likes
  • 3 in conversation