Formatting Question

[RussellNonBrand]

Hey Everyone,

I can't seem to find this... I am setting up some macros to automate reports to get on to more interesting projects... I am using the INTX function for date ranges.

i.e. beginning and end of a month (in a data null step)

call symputx('BEGDATE' , "'" || put(intnx('month',today(),-13,'BEGIN'),yymmdd10.) || "'");

call symputx('ENDDATE' , "'" || put(intnx('month',today(),-1,'END'),yymmdd10.) || "'");

Output '2010-10-01' or '2011-09-30'

This works great.  However, I have another date value that is stored as YYYYMM (i.e. 201010 or 201109 from above examples).

So I wrote the following:

call symputx('prev_beg' , "'" || put(intnx('year' ,today(),-1),YEAR.)|| put(intnx('month',today(),-1,'END'),MONTH.) || "'");

call symputx('prev_end' , "'" || put(intnx('year' ,today(),0),YEAR.) || put(intnx('month',today(),-2,'END'),MONTH.) || "'");

However, if the month is Jan - Sep (1-9), it doesn't give a preceeding zero.  Is there any known format to include this?  Or do I just need to use some conditional formatting?

Google doesn't seem to have the answer... so if you ever have had to do this I appreciate any input!

Thanks!

--Russell                   

[Tom · Accepted solution]

Why not use the YYMMDDN8. format and just take the first 6 characters?

substr(put(intnx('month',today(),-13,'BEGIN'),yymmddn8.),1,6)

[RussellNonBrand]

Works great!  Sometimes the easiest answers are overlooked... haha.  Thanks!

[Peter_C]

Russell

I am a bit late spotting this, but

even simpler is the date format YYMMN.  needing no substringing -

as discovered by the "format hunter" data step

data formats_found_in_hunt ;

   set  sashelp.vformat ;

   where fmttype = 'F' ;        * just formats; 

   where also fmtname ne: '$' ; * and not character formats;

   length nd $40 ;

  nd = putn( "25-Sep-2010"d,  fmtname );

   if _error_ then do ;

      _error_ = 0 ;

      delete ;

   end ;

   if left(nd) =: '201009' ;

   put fmtname= nd= ;

run;

this reports to the log

54   run;

fmtname=B8601DA nd=20100925

NOTE: Argument 2 to function PUTN at line 47 column 9 is invalid.

fmtname=YYMMDDN nd=20100925

fmtname=YYMMN nd=201009

NOTE: There were 587 observations read from the data set SASHELP.VFORMAT.

      WHERE (fmttype='F') and (fmtname not =: '$');

The NOTE is interesting, but the important thing is

fmtname=YYMMN nd=201011

then you can adapt

call symputx('prev_beg' , "'" || put(intnx('year' ,today(),-1),YEAR.)|| put(intnx('month',today(),-1,'END'),MONTH.) || "'");

call symputx('prev_end' , "'" || put(intnx('year' ,today(),0),YEAR.) || put(intnx('month',today(),-2,'END'),MONTH.) || "'");

to

call symputx( 'prev_beg2', quote( put( intnx( 'month', today(), -2  ), yymmn.))) ;

call symputx( 'prev_end2', quote( put( intnx( 'month', today(), -13 ), yymmn.))) ;

but that quote() function seems unable to override its "default quote" double quote, so

call symputx( 'prev_beg3', "'" !! put( intnx( 'month', today(), -2  ), yymmn.) !! "'" ) ;

call symputx( 'prev_end3', "'" !! put( intnx( 'month', today(), -13 ), yymmn.) !! "'" ) ;

I do prefer to avoid a data step for this when it seems not much more syntax to do it in a %LET like

%let prev_beg4 = %str(%')%sysfunc( intnx( month, "&sysdate"d, -13 ), yymmN )%str(%') ;

%let prev_end4 = %str(%')%sysfunc( intnx( month, "&sysdate"d, -2  ), yymmN )%str(%') ;

%put previously from &prev_beg4 to &prev_end4 ;

which reports to my SASlog

88   %put previously from &prev_beg4 to &prev_end4 ;

previously from '201010' to '201109'

[RussellNonBrand]

Wow Peter - that is too cool!!!!  Thank you so much, I love these little tips and tricks most people never hear about!