Hi,
I have a logic problem and an issue with creating a macro that will take a previous month end date from a specified date.
for example:
if i have a date at any point before the month end i'l like to create a USAGE_TIMESTAMP variable of the previous month end.
otherwise if i have a date at the month end i'd like the USAGE_TIMESTAMP to be at the current month end.
I'm not sure if the above is possible to automate but an example would be as follows:
Middle of the month example:
%LET DATE='15NOV2011'D;
%LET TIMESTAMP=2011NOV15;
%LET USAGE_TIMESTAMP =2011OCT31
Month end example:
%LET DATE='30NOV2011'D;
%LET TIMESTAMP=2011NOV30;
%LET USAGE_TIMESTAMP =2011NOV30
i've tried the following for USAGE_TIMESTAMP, it gives me the right date, but i tried using it in a data step (data A.FILE_&USAGE_TIMESTAMP) and i get an error because it is outputting the formula as a text rather than the "2011OCT31":
%LET USAGE_TIMESTAMP = INTNX('MONTH',&DATE,-1,'END') ;
Thank you for your help.
I think you want to do something like:
%LET DATE=%sysevalf('30NOV2011'd);
%LET TIMESTAMP=%sysevalf('30NOV2011'd);
%LET USAGE_TIMESTAMP = %sysfunc(INTNX(MONTH,&DATE,-1,end));
%put &usage_timestamp;
A couple of issues.
1) I am not sure there is a predefined format that reverses the year and day positions of the DATE9 format.
2) To call a function in macro code you need to use %SYSFUNC(). Also the text fields (like MONTH in your example) do not require the quotes in macro code.
%let date='15NOV2011'd;
%let timestamp=%sysfunc(putn(&date,yymmddn8.));
%let usage_timestamp=%sysfunc(intnx(MONTH,&date,-1,END),yymmddn8.);
You could increment the current date by one in a data step, that would always put you to the previous month end.
%LET TIMESTAMP=29NOV2011;
DATA _NULL_;
SASDate=INPUT("&TIMESTAMP",date9.)+1;
SASDate1=INTNX('MONTH',SASDate,-1,'END');
CALL SYMPUT("usage_timestamp",PUT(SASDate1, date9.));
RUN;
DATA FILE_&usage_timestamp;
PreviousMonthEnd="&usage_timestamp";
RUN;
I think this macro produces the effect you desire:
%macro timestamp(Date); %LET DATE=%sysevalf("&date"d); %if %sysfunc(month(&date)) =%sysfunc(month(%eval(&date+1))) %then %LET Stamp= %sysfunc(INTNX(MONTH,&DATE,-1,end)); %else %let Stamp=&Date; %sysfunc(cats(%sysfunc(PUTN(&Stamp,YYMON7.)),%sysfunc(day(&Stamp)))); %mend;
This macro utility can be used like a macro function, that is, it can be used in-line with other SAS code to return the text value for the usage_timestamp (YYYYMMMDD). After compling the macro, try it out with various values - perhaps using code similar to this:
%put NOTE: USAGE_TIMESTAMP = %timestamp(20NOV2011); %put NOTE: USAGE_TIMESTAMP = %timestamp(30NOV2011); %put NOTE: USAGE_TIMESTAMP = %timestamp(20DEC2011); %put NOTE: USAGE_TIMESTAMP = %timestamp(31DEC2011); %put NOTE: USAGE_TIMESTAMP = %timestamp(02JAN2012);
I hope this helps.
Hi DanglyticS, Try this out:
/*------------ START SAS CODE EXAMPLE ------------- */
DATA _NULL_;
DT = TODAY();
TIMESTAMP = DT;
IF DT = INTNX('MONTH',DT,1)-1
THEN DO;
USAGE_TIMESTAMP = DT;
END;
ELSE DO;
USAGE_TIMESTAMP = INTNX('MONTH',DT,0)-1;
END;
PUT USAGE_TIMESTAMP=; /* PRINT DATE IN LOG TO MAKE SURE IT IS CORRECT FORMAT */
FORMAT USAGE_TIMESTAMP DATE9.;
RUN;
/*-------------------- END SAS CODE EXAMPLE ---------------- */
I hope this helps you out.
Jon S.
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.
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.