05-18-2016 11:01 PM - last edited on 05-19-2016 12:11 AM by Reeza
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.
05-18-2016 11:23 PM - edited 05-18-2016 11:23 PM
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);
05-18-2016 11:26 PM
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.
05-18-2016 11:45 PM
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.
05-18-2016 11:51 PM
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.
05-19-2016 12:36 AM
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);
05-19-2016 12:53 AM
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.
05-19-2016 02:45 AM
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.
05-19-2016 04:49 AM
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.