Help using Base SAS procedures

2 Levels of Date Formats in one column

Reply
Super Contributor
Posts: 291

2 Levels of Date Formats in one column

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

Re: 2 Levels of Date Formats in one column

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

Re: 2 Levels of Date Formats in one column

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.
SAS Super FREQ
Posts: 8,745

Re: 2 Levels of Date Formats in one column

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]
Super Contributor
Posts: 291

Re: 2 Levels of Date Formats in one column

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.
SAS Super FREQ
Posts: 8,745

Re: 2 Levels of Date Formats in one column

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

Re: 2 Levels of Date Formats in one column

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