I want to produce an ods report that groups data by month as an across variable but if it is the current month, then group by weeks instead. Is this possible and if so, what is the best proc to use?
Follow that with any statistical summary as most group by formatted value.
FORMAT lets you invoke different formats based on the range of values in your variable, like "before this month" can define a date range.
Honestly, I don't see how you are going to use a PICTURE format to satisfy programming for a tailored columnar report with week-start and month-start (or end) date column headings having some statistics values in the detail report rows. The input data needs to be manipulated to derive a week-start date (using INTNX) for current period observations and a separate, additional SAS file for the "historical" date values beyond the current month period.
The documentation is not very clear. The syntax you want might look like this:
proc format ;
0 - &dtLow = 'W%w' (datatype=datetime)
&dtHigh - high = '%b%Y'(datatype=datetime);
Also, the default formatted length above is 4, so either increase this or use yrWk8.
Thanks Peter, I think I recall this is supposed to be the case, but was not able to found it in the documentation.
proc format ;
value x 0=time.
x=0; put x= x.;
x=1; put x= x.;
doesn't work. How would you write this? My time to learn!
/*-- Create a new format that will parse dates as month yr if they are less than the High Value --*/
0 - &dtLow = '%b%Y' (datatype=date)
&dtHigh - high = '%m-%d'(datatype=date);
To embed (or "nest") a format place it within [ ] as the "label" [pre]%let tooEarly = 1jan2000 ;
%let lastMonth= %sysfunc( intnx( month,"&sysdate9"d, -1, e ), date9 );
%let latest = %sysfunc( intnx( year, "&sysdate9"d, 0, e ), date9 );
proc format ;
value mulDt( default=11 )
low - "&earliest"d = 'err:Too old'
"&earliest"d - "&lastMonth"d = [yyMon.]
"&lastMonth"d - "&latest"d = [week.]
"&latest"d - high = 'futures'
run ;[/pre] then use format MULDT. on a format statement for the class date variable.
You will need to use a combination of PROC TRANSPOSE, after separating current period observation into a separate file, perform the transpose, and then merge the two files back together. I suspect you can use PROC PRINT for generating the horizontal observations having 'week period' and 'month period' variable values, as generated by PROC TRANSPOSE.
Review the PROC TRANSPOSE documentation with reference to the BY and ID statements and the PREFIX= parameter. Also, for PROC PRINT, you will want to explore using the VAR statement and employ the use of a "stem variable prefix" ending in a colon character allowing you to abbreviate your variable list.
The other option is to use a DATA step for full-control with PUT and column-position report generation.
Search the SAS support http://support.sas.com/ website for SAS-hosted documentation and supplemental technical and conference topic papers.