topic Re: Report showing Months and Weeks in SAS Procedures

Report showing Months and Weeks

CameronLawson — Wed, 17 Jun 2009 14:24:08 GMT

Hi All,
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?

Thanks in advance

Re: Report showing Months and Weeks

Peter_C — Wed, 17 Jun 2009 17:10:18 GMT

proc FORMAT.
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.

Re: Report showing Months and Weeks

sbb — Wed, 17 Jun 2009 21:24:32 GMT

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.

Scott Barry
SBBWorks, Inc.

Re: Report showing Months and Weeks

CameronLawson — Wed, 17 Jun 2009 21:35:18 GMT

So I would need to set up a picture format that looked formatted any date not in the current month as something like monyy7. and anything in the current month as a week number?

The beauty of forums....I would not have thought of doing it in such a gloriously simple way as that!

It would be something like...(I'll be testing this at work)

%let dtLow = %sysfunc(datetime()-(8*86400));
%let dtHigh = %sysfunc(datetime()-(7*86400));
proc format;
picture yrWk
0 - &dtLow = ''
&dtHigh - high = ''
;
run;

Thanks! Time to dust off the old course notes to remember how to construct picture formats again....

Re: Report showing Months and Weeks

sbb — Wed, 17 Jun 2009 21:49:11 GMT

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.

Scott Barry
SBBWorks, Inc.

Re: Report showing Months and Weeks

ChrisNZ — Wed, 17 Jun 2009 23:13:29 GMT

The documentation is not very clear. The syntax you want might look like this:
[pre]
proc format ;
picture yrWk
0 - &dtLow = 'W%w' (datatype=datetime)
&dtHigh - high = '%b%Y'(datatype=datetime);
run;
[/pre]
Also, the default formatted length above is 4, so either increase this or use yrWk8.

Re: Report showing Months and Weeks

CameronLawson — Thu, 18 Jun 2009 02:46:53 GMT

Sir, you impress as always.
Thanks again.

Re: Report showing Months and Weeks

Peter_C — Thu, 18 Jun 2009 11:57:12 GMT

No pictures needed unless SAS doesn't already have suitable formats for week and month. The label for the formating a range, can be another format name.

Re: Report showing Months and Weeks

ChrisNZ — Thu, 18 Jun 2009 22:28:20 GMT

Thanks Peter, I think I recall this is supposed to be the case, but was not able to found it in the documentation.
[pre]
proc format ;
value x 0=time.
1=date.;
data t;
x=0; put x= x.;
x=1; put x= x.;
run;
[/pre]
doesn't work. How would you write this? My time to learn! 🙂

Re: Report showing Months and Weeks

CameronLawson — Fri, 19 Jun 2009 02:10:17 GMT

You can use a format within a format? Really?

Chris,
I ended up using your suggestion

data _null_;
x = 19;
x1 = 18;

dtLow = date()- x;
dtHigh = date()-x1;

call symputx('dtLow',dtLow);
call symputx('dtHigh',dtHigh);
run;

%put &dtLow;
%put &dtHigh;

/*-- Create a new format that will parse dates as month yr if they are less than the High Value --*/
proc format;
picture yrWk
0 - &dtLow = '%b%Y' (datatype=date)
&dtHigh - high = '%m-%d'(datatype=date);
run;

Re: Report showing Months and Weeks

Peter_C — Fri, 19 Jun 2009 13:50:45 GMT

really, simple
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.

Re: Report showing Months and Weeks

ChrisNZ — Sun, 21 Jun 2009 23:27:03 GMT

Square brackets! I don't recall ever seeing this before, thank you Peter. 🙂
Glad it helped, Cameron. You also have Peter's syntax to consider now.

Re: Report showing Months and Weeks

CameronLawson — Sun, 21 Jun 2009 23:34:56 GMT

wow you learn something new everyday! Thanks Peter and Chris