BookmarkSubscribeRSS Feed
Danglytics
Calcite | Level 5

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.

5 REPLIES 5
art297
Opal | Level 21

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;

Tom
Super User Tom
Super User

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.);

TG_WPAFB
Calcite | Level 5

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;

SASJedi
SAS Super FREQ

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.

Check out my Jedi SAS Tricks for SAS Users
JonS_
Calcite | Level 5

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.

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!

How to Concatenate Values

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.

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
  • 5 replies
  • 1338 views
  • 0 likes
  • 6 in conversation