How do I automate running Macro without manually typing in date for new monthend date

Reply
Occasional Contributor
Posts: 5

How do I automate running Macro without manually typing in date for new monthend date

[ Edited ]

Hi There

 

I have SAS macro code where I have go change the date each month to run it. 

 

How do I write a piece of code or macro so it checks the source data date, and if it has created month end data then run the Macro code automatically without having to manaually go into the code and changing the date for new month end date?

 

e.g %runmth('30Apr2016'd);  <<<  I have to change the date manually and run the Macro each month.

 

I have attached my code in the attachment.

Super User
Posts: 3,105

Re: How do I automate running Macro without manually typing in date for new monthend date

[ Edited ]

Well if the date you require is last month's end date then something like this should work:

 

 

%let End_Last_Month = %sysfunc(intnx(MONTH, %sysfunc(today()), -1, END), date9.);
%put &End_Last_Month.;

%runmth("&End_Last_Month"d);

 

Occasional Contributor
Posts: 5

Re: How do I automate running Macro without manually typing in date for new monthend date

Sorry I don't need last month end date, I would like to check if the data source in the source table is updated then run the macro with that date.

Super User
Posts: 3,105

Re: How do I automate running Macro without manually typing in date for new monthend date

Can you supply a simple SQL query that will give you the date you want then? It is not obvious from your code.

 

BTW - be careful posting actual work code. You could be breaking confidentiality rules.  

Occasional Contributor
Posts: 5

Re: How do I automate running Macro without manually typing in date for new monthend date

set @MAXFEEDDATE = (select MAX(Dim_SnapshotDate_key) from GroupDW.dbo.Fact_CustomerSummarySnapshot)

 

Oh yes I would like to use max date from this table, so using this date as the date I want to input into the SAS macro.

Super User
Posts: 3,105

Re: How do I automate running Macro without manually typing in date for new monthend date

This should get you pretty close. I don't know if the column you are querying is a DATE datatype or not so you might have to tweak this a bit:

 

proc sql noprint;
  connect to SQLSVR as sqlGDWSP (Datasrc=focis);
  select put(Dim_SnapshotDate_key_Max, date9.) into :Dim_SnapshotDate_key_Max
  from connection to SQLSVR
  (select MAX(Dim_SnapshotDate_key) as Dim_SnapshotDate_key_Max  from GroupDW.dbo.Fact_CustomerSummarySnapshot);
quit;

%runmth(&Dim_SnapshotDate_key_Max);
 
Occasional Contributor
Posts: 5

Re: How do I automate running Macro without manually typing in date for new monthend date

The date is in this format and data type is  interger.  20160430.  So what would the new code look like?  Sorry I am terrible at date syntex.

Super User
Posts: 5,256

Re: How do I automate running Macro without manually typing in date for new monthend date

You ned to combine the put() and input() function to get the result in SAS date format. Then use the format= option to store the macro variable in date9 format.

 

Being terrible at something isn't an excuse for not trying to find information/learn yourself.

Use google and/or search facility of support.sas.com.

Data never sleeps
Super User
Posts: 5,256

Re: How do I automate running Macro without manually typing in date for new monthend date

Instead of SET use the SAS SQL select into mechanism to store the result in a macro variable.
Data never sleeps
Occasional Contributor
Posts: 5

Re: How do I automate running Macro without manually typing in date for new monthend date

Could you please give me an example code please?  Sorry I am terrible with SAS dates and macros.

Super User
Super User
Posts: 7,401

Re: How do I automate running Macro without manually typing in date for new monthend date

Sorry, the question is unclear, so is the process.  Why do you have macro code, what date needs to be changed each time and how is that derived?  What does the functional design specification for the code say the thing should be doing etc?  The process, from what you have stated is something like:

Calculate a date value

Run some code using this date value

 

Maybe elaborate on those two points.

Ask a Question
Discussion stats
  • 10 replies
  • 482 views
  • 3 likes
  • 4 in conversation