Help using Base SAS procedures

Report showing Months and Weeks

Reply
Contributor
Posts: 66

Report showing Months and Weeks

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
Valued Guide
Posts: 2,174

Re: Report showing Months and Weeks

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.
Contributor
Posts: 66

Re: Report showing Months and Weeks

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....
Super Contributor
Super Contributor
Posts: 3,174

Re: Report showing Months and Weeks

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.
PROC Star
Posts: 1,558

Re: Report showing Months and Weeks

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.
Contributor
Posts: 66

Re: Report showing Months and Weeks

Sir, you impress as always.
Thanks again.
Valued Guide
Posts: 2,174

Re: Report showing Months and Weeks

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.
PROC Star
Posts: 1,558

Re: Report showing Months and Weeks

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! Smiley Happy
Contributor
Posts: 66

Re: Report showing Months and Weeks

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;
Valued Guide
Posts: 2,174

Re: Report showing Months and Weeks

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.
PROC Star
Posts: 1,558

Re: Report showing Months and Weeks

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

Re: Report showing Months and Weeks

wow you learn something new everyday! Thanks Peter and Chris
Super Contributor
Super Contributor
Posts: 3,174

Re: Report showing Months and Weeks

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.
Ask a Question
Discussion stats
  • 12 replies
  • 165 views
  • 0 likes
  • 4 in conversation