BookmarkSubscribeRSS Feed
CameronLawson
Obsidian | Level 7
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
12 REPLIES 12
Peter_C
Rhodochrosite | Level 12
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.
CameronLawson
Obsidian | Level 7
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....
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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.
ChrisNZ
Tourmaline | Level 20
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.
CameronLawson
Obsidian | Level 7
Sir, you impress as always.
Thanks again.
Peter_C
Rhodochrosite | Level 12
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.
ChrisNZ
Tourmaline | Level 20
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! 🙂
CameronLawson
Obsidian | Level 7
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;
Peter_C
Rhodochrosite | Level 12
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.
ChrisNZ
Tourmaline | Level 20
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.
CameronLawson
Obsidian | Level 7
wow you learn something new everyday! Thanks Peter and Chris
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 12 replies
  • 990 views
  • 0 likes
  • 4 in conversation