BookmarkSubscribeRSS Feed
engin
Calcite | Level 5

Hi All,

 

I tried to build a query which is:

 

             case when t1.date between "31AUG2015"d - 365 and "31aug2015"d then 1 else 0 end

 

but since I have to run this query every month-end. I have to change "31aug2015" to "30sep2015" next month and so ever.

 

Is there any prompt technique to handle this automatically?

 

Thanks a lot

 

 

2 REPLIES 2
DartRodrigo
Lapis Lazuli | Level 10

Hi mate,

 

You can create a macro that contains the date, and if you only need to run this process in the end of the month you can do this:

 

 

%let mondate = %sysfunc(date(),date9.);
%put &mondate.;

/*Check the SAS Log to see the result*/

 

 

But if you need to execute where you don't know when (everyday) you may do the following:

 

 

%GLOBAL YEARDT
		ANO
		MES
		INTERVALO
		ANOI
		MESI
		BASES;

%LET YEARDT = %SYSFUNC(DATE(),YYMMP10.);
%LET ANO	= %SUBSTR(&YEARDT.,1,4);
%LET MES	= %SUBSTR(&YEARDT.,6,2);
%PUT &ANO&MES.;

%MACRO APPEND(NUM);
DATA NEED;
   SET
%DO I = 0 %TO #
%LET INTERVALO = %SYSFUNC(PUTN(%SYSFUNC(INTNX(MONTH,%SYSFUNC(DATE()),-&&I.)),YYMMP10.));
%LET ANOI	= %SUBSTR(&INTERVALO.,1,4);
%LET MESI	= %SUBSTR(&INTERVALO.,6,2);
%LET BASES  = BASE_&ANOI&MESI.;
%PUT &BASES.;
	&BASES.
%END;;
RUN;
%MEND; 
%APPEND(3);

/*This code appends the last three months of tables*/

 

And if you need to use the macro in a date the syntax is like this "&macro."d or "&macro."dt

 

In the second code you only need to change the number in

%APPEND(/*Put here the number you want*/);

 

Hope this will help you.

Matthijs
Obsidian | Level 7

Hi Engin,

 

Couple of questions:

1. What does the query do? (besides a 'case when'; what are you trying to accomplish with the results of the query)

2. When does the query run? (does it always run on the last day of the month? Or also on onther days/dates?

3. What is the reason you're using a prompted value?

 

I believe there are multiple solutions depending on the questions above.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 1192 views
  • 0 likes
  • 3 in conversation