topic Re: Formatting Question in SAS Enterprise Guide

Formatting Question

RussellNonBrand — Wed, 09 Nov 2011 16:39:33 GMT

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

Formatting Question

Tom — Wed, 09 Nov 2011 17:30:14 GMT

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

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

Formatting Question

RussellNonBrand — Wed, 09 Nov 2011 18:55:05 GMT

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

Re: Formatting Question

Peter_C — Fri, 25 Nov 2011 10:56:35 GMT

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

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'

Re: Formatting Question

RussellNonBrand — Fri, 25 Nov 2011 15:17:33 GMT

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