topic Re: Divide month into 3 parts in timestamp in SAS Programming

Divide month into 3 parts in timestamp

Swapnil_21 — Sat, 28 Sep 2019 08:51:17 GMT

Hi,

I need to divide current month in 3 parts of days.

1-10 in part1

11-20 in part2

21-last day of month in part3. So there are 6 macro variables to be created like

01SEP2019:00:00:00

10SEP2019:00:00:00

11SEP2019:00:00:00

20SEP2019:00:00:00

21SEP2019:00:00:00

30SEP2019:00:00:00

I have managed to create first and last day of the month as below. But somehow unable to create remaining variables. Below is the code I have tried. Thanks in advance

DATA _NULL_;
CALL SYMPUT('a',CATS(put(INTNX("MONTH",today()-2,0,"B"),DATE9.),":00:00:00"));
CALL SYMPUT('b',CATS(put(INTNX("MONTH",today()-2,0,"E"),DATE9.),":00:00:00"));
CALL SYMPUT ('ORA_PREV_MON_E',CATS(put(INTNX("MONTH",today(),-1,"E"),DATE9.),":00:00:00"));
/*CALL SYMPUT ("ORA_CURR_MON",cat("'",PUT(MONTH,date9.),"'"));;*/
RUN;
%put &a &b &ORA_PREV_MON_E;

Re: Divide month into 3 parts in timestamp

Patrick — Sat, 28 Sep 2019 10:05:35 GMT

If I understand this right then below the code you're asking for.

data _null_;
  dttm=intnx('dtmonth',datetime(),0,'b');
  /* 1st of month */
  call symputx('dttm_first',put(dttm,datetime20.),'g');
  /* 10th of month */
  call symputx('dttm_10',put(intnx('dtday',dttm,9,'b'),datetime20.),'g');
  /* 11th of mont */ 
  call symputx('dttm_11',put(intnx('dtday',dttm,10,'b'),datetime20.),'g');
  /* 20th of mont */ 
  call symputx('dttm_20',put(intnx('dtday',dttm,19,'b'),datetime20.),'g');
  /* 21th of mont */ 
  call symputx('dttm_21',put(intnx('dtday',dttm,20,'b'),datetime20.),'g');
  /* last of mont */ 
  call symputx('dttm_last',put(intnx('dtmonth',dttm,0,'e'),datetime20.),'g');
  stop;
run;
%put &=dttm_first;
%put &=dttm_10;
%put &=dttm_11;
%put &=dttm_20;
%put &=dttm_21;
%put &=dttm_last;

DTTM_FIRST=01SEP2019:00:00:00
DTTM_10=10SEP2019:00:00:00
DTTM_11=11SEP2019:00:00:00
DTTM_20=20SEP2019:00:00:00
DTTM_21=21SEP2019:00:00:00
DTTM_LAST=30SEP2019:23:59:59

Re: Divide month into 3 parts in timestamp

Shmuel — Sat, 28 Sep 2019 12:22:26 GMT

@Swapnil_21 , @Patrick - You created macro variables holding the limit datetime of each part,

More available would be to know what usage is done with those macro variables?

Maybe if you look for categories: A B C as 1st, 2nd, 3rd part of a month - is it not enough to check

the day only:

if day(<date or datetime>) le 10 then category = 'A'; else
if day(<date or datetime>) le 20 then category = 'B'; else category = 'C';

Does the data have one month data only? what if there are more then one month? what if it is not September?

If you want to use the macros as labels in a report or a graph - are they not too long ?

Re: Divide month into 3 parts in timestamp

hashman — Sat, 28 Sep 2019 18:44:04 GMT

@Swapnil_21:

Heed what @Shmuel has written and look no further than @Patrick's response if you need macro variables (though I can't see what for).

Normally, in light of your question a typical task would go along these lines: Given a SAS datetime value, generate a variable to indicate which part of the month the datetime belongs. Its values would be 1 for the days 1-10, 2 for 11-20, and 3 for 21+.

When you have to deal with ranges, the SAS in/formats are the tools specifically designed for the purpose; in fact, any in/format argument is always a range (even in the case its endpoints are the same). Hence, a natural way of addressing the task would be:

proc format ;                                    
  value monpart low-10=1 11-20=2 21-high=3 ;     
run ;                                            
                                                 
data _null_ ;                                    
  input dt datetime. ;                           
  monpart = put (day (datepart (dt)), monpart.) ;
  put monpart= ;                                 
  cards ;                                        
01SEP2019:00:00:00                               
10SEP2019:00:00:00                               
11SEP2019:00:00:00                               
20SEP2019:00:00:00                               
21SEP2019:00:00:00                               
30SEP2019:00:00:00                               
;                                                
run ;

The expression for MONPART above generates a character variable $1. In/formats cannot generate a numeric variable to numeric variable (number-to-number) correspondence all by themselves due to their nature, so if you want a numeric variable as the response, change the expression to:

  monpart = input (put (day (datepart (dt)), monpart.), 1.) ;

On the other hand, when the ranges are numeric and equidistant, as in your case, arithmetic can be used to relate a number-to-number directly, e.g.:

data _null_ ;                                
  input dt datetime. ;                       
  monpart = ceil (day (datepart (dt)) / 10) ;
  put monpart= ;                             
  cards ;                                    
01SEP2019:00:00:00                           
10SEP2019:00:00:00                           
11SEP2019:00:00:00                           
20SEP2019:00:00:00                           
21SEP2019:00:00:00                           
30SEP2019:00:00:00                           
;                                            
run ;

Kind regards

Paul D.

Re: Divide month into 3 parts in timestamp

ballardw — Mon, 30 Sep 2019 15:27:07 GMT

@Swapnil_21 wrote:

Hi,

I need to divide current month in 3 parts of days.

1-10 in part1

11-20 in part2

21-last day of month in part3. So there are 6 macro variables to be created like

01SEP2019:00:00:00

10SEP2019:00:00:00

11SEP2019:00:00:00

20SEP2019:00:00:00

21SEP2019:00:00:00

30SEP2019:00:00:00

I have managed to create first and last day of the month as below. But somehow unable to create remaining variables. Below is the code I have tried. Thanks in advance

DATA _NULL_;
CALL SYMPUT('a',CATS(put(INTNX("MONTH",today()-2,0,"B"),DATE9.),":00:00:00"));
CALL SYMPUT('b',CATS(put(INTNX("MONTH",today()-2,0,"E"),DATE9.),":00:00:00"));
CALL SYMPUT ('ORA_PREV_MON_E',CATS(put(INTNX("MONTH",today(),-1,"E"),DATE9.),":00:00:00"));
/*CALL SYMPUT ("ORA_CURR_MON",cat("'",PUT(MONTH,date9.),"'"));;*/
RUN;
%put &a &b &ORA_PREV_MON_E;

You may strongly want to consider 1) making these DATE values if the time is always 00:00:00 and

2) looking at the TENDAY interval option for the INTNX and INTCK functions: I use October in the following example to demonstrate what happens in months with 31 days.

data example;
   do day = 1,9,10,11,19,20,21,29,30,31;
      date= mdy(10,day,2019);
      date2 = intnx('tenday',date,0,'b');
      format date date2 date9.;
      output;
   end;
run;