BookmarkSubscribeRSS Feed
Bill
Quartz | Level 8
I have some data that I've summarized by month for the first six months and then by week for the seventh month. The reworked the file and the date values now reside in one column as sas dates. I want to display the month values with an monyy7. format and the week values with a date7. format in a proc tabulate report. Is there a way to apply 2 formats against one column? If yes, how is that to be done? (I've tried adding a new column with the dates in the correct format (with a put statement) but then the column ordering becomes a problem).
Thank you!
6 REPLIES 6
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
Suggest you add a "new" SAS DATE variable and use INTNX function in a DATA step assignment to decrement/increment it to the appropriate boundary interval from the original SAS DATE variable you already have in your data. Then use the desired display OUTPUT format. You can then use each variable as needed for summary/break identification in your PROC TABULATE code.

Scott Barry
SBBWorks, Inc.
Bill
Quartz | Level 8
Thanks Scott.
I have all that but now I need to display the same column of data with two different formats, depending on the value.

proc tabulate data=SlitLoss4;
class Op_No Grd_Cd Period;
var EdgeWtLoss;
table (Op_No=' ' all) *
(Period='Month / Week Of' all),
(Grd_Cd='Grade' all) *
EdgeWtLoss=' Weight Loss ' * sum=''
;
Period is the column with the date values that I would like to display with 2 different formats.
Cynthia_sas
SAS Super FREQ
Hi:
The problem that I see is that your PERIOD variable is a CLASS variable and must be formatted with a FORMAT statement in PROC TABULATE. But you want to make a decision about what format to use based on some logic that might be best implemented in a DATA step program.

PROC TABULATE doesn't care whether the CLASS variable is a character variable or a numeric variable. In this case, I'd be very tempted to make a CHARACTER class variable that was the result of making my decision for which format to use in a DATA step program.

This program doesn't create a table with the same structure as your TABULATE program; however, the -general- idea of making a character variable based on some criteria is shown. In my program, for months 1-11, I use the MONYY7 format for my CATDATE variable and for month 12, I use the DATE7. format.

The program has 2 steps -- one shows the data sorted by the DATE variable in ascending order and the other step shows the data sorted by descending order (and using ORDER=DATA in the CLASS statement).

cynthia

[pre]
** make some fake data using SASHELP.CLASS;
data makedata;
set sashelp.class;
where age le 12;
rowcat = cats(upcase(substr(name,1,2)),'XYZ');
year = 2009;
do month = 1 to 12 by 1;
do day = 1 to 5 by 1;
date = mdy(month,day,year);
if month le 11 then do;
cat = 'Month/Year Values';
catdate = put(date,monyy7.);
end;
if month = 12 then do;
cat='Weekday Values';
catdate = put(date,date7.);
end;
numvar = age*_n_;
output;
end;
end;
run;

/*
** What does data look like;
proc print data=makedata;
run;
*/

ods listing close;
ods html file='make_catdate.html' style=sasweb;

proc sort data=makedata;
by date;
run;

proc tabulate data=makedata f=comma8.;
title '1) Sorted by Ascending DATE';
class rowcat;
class cat catdate /order=data;
var numvar;
table rowcat all='Total',
cat=' '*(catdate=' '*numvar=' '*sum=' ');
run;

proc sort data=makedata;
by descending date;
run;

proc tabulate data=makedata f=comma8.;
title '2) Sorted by Descending DATE';
class rowcat;
class cat catdate /order=data;
var numvar;
table rowcat,
cat=' '*(catdate=' '*numvar=' '*sum=' ');
run;

ods html close;
[/pre]
Bill
Quartz | Level 8
Thank you Cynthia. A most helpful response. Your teaching strength shows up in every post - you taught me something new again. This time it was - class cat catdate /order=data; I did not know that an order= clause could be placed against a class statement and so apply the order "rule" to only a subset of the data.
Cynthia_sas
SAS Super FREQ
Bill:
Glad to help! And I'm glad the response was useful to you.

TABULATE allows multiple CLASS statements (and multiple VAR statements) so you can apply different options to different VAR or CLASS variable headers/values (such as MISSING or ORDER= or PRELOADFMT, etc for CLASS variables).
CLASS Statement doc:
http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/a002473739.htm
VAR Statement doc:
http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/a002473743.htm

ORDER= is not -exactly- ordering a subset of the data -- it's controlling the ORDER of the CLASS (or category) headers or rowheaders in the output table. Another reason to use multiple CLASS variables would be to use different STYLE= options, so you could make different header cells for the CLASS variables have different style characteristics.

cynthia
Peter_C
Rhodochrosite | Level 12
sorry this seems a bit late but no-one seems to have thought of a natural solution.

> I have some data that I've summarized by month for
> the first six months and then by week for the seventh
> month. The reworked the file and the date values now
> reside in one column as sas dates. I want to display
> the month values with an monyy7. format and the week
> values with a date7. format

An attractive feature of the SAS language is user-defineable formatting.
This allows you to choose the format for a range of values (not just the formattted value).
So, for recent events you can summarise (or tabulate) in detail, while providing a broad interval for older events.

An important benefit in SAS is the easy facility to establish interval dates.
Today is provided by function today() or date()
The beginning of last week is intnx( 'week', today(), -1 )
The beginning of last month is intnx( 'month', today(), -1 )
These examples have been written for the data step environment. As parameters the syntax is slightly different. A date constant for today is '07Apr2010'd. This style can be generated by the SAS Macro Language with syntax like[pre] %let today = %sysfunc( today(), date9 ) ;[/pre] That %sysfunc() wrapper supports almost all stand-alone data step functions (and none that depend on the data step environment like lag() and vlabel() ). The second parameter in that %sysfunc() call i.e. date9 is the format I use to convert the numeric result of the function call which is the first parameter of %sysfunc() into text (for the "string-only"macro environment)
So, date parameters can be calculated ready to use when choosing the varieties of date formatting you want.

first 6 months by month, thereafter by week [pre] %let ref1date = 31dec2008 ; **** just for testing ;
%let ref2date = %sysfunc( intnx( month, "&ref1date"d, 6, sameday ), date9 ) ;
proc format cntlout= specdat_cntl ;
value specDat low - "&ref1date"d = [year4.]
"&ref1date"d - "&ref2date"d = [monYY7.]
"&ref2date"d - high = [date7.]
;
run ;[/pre]* even though present in more than one date range the "&ref1date"d and "&ref2date"d will not overlap. By default the upper bound is included and the lower bound is excluded, when format ranges finish and start on the same number. Check out the cntlout= dataset specDat_cntl columns sexcl (start exclude) eexcl (end exclude) ;

Then just add a statement to Bill's proc tabulate to associate specDat. format with the variable Period as below.

proc tabulate data=SlitLoss4;
class Op_No Grd_Cd Period;
format period specDat. ;
var EdgeWtLoss;
table (Op_No=' ' all) *
(Period='Month / Week Of' all),
(Grd_Cd='Grade' all) *
EdgeWtLoss=' Weight Loss ' * sum=''
;

hope it proves useful
PeterC

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
  • 6 replies
  • 2021 views
  • 0 likes
  • 4 in conversation