The SAS Output Delivery System and reporting techniques

Format date in proc tabulate using proc format

Reply
Occasional Contributor
Posts: 16

Format date in proc tabulate using proc format

Hi All, trying to find possible solution to my issue. Any assistance would be appreciated.
I have check_dt that is in format mmyy7. (JAN2009,DEC2008, NOV2008 etc) . However, there is also possibility of having check_dt=SEP9999 (month9999 year).
If that is the case, if check_dt has year(check_dt)=9999 then I want to have new_check_dt=n/a, otherwise new_check_dt=check_dt.
That changes from date format to character if done in data step. So, I thought I could do something like this
%let lowdate = '31DEC2008'd;
%let highdate = '01JAN2009'd;

proc format;
value nadate low-&lowdate = [monyy7.]
&highdate-high = ' N/A';
run;
Then....in proc tabulate steps...are something like this
class
classlev
var
table check_dt='Check Date'*f=nadate.

But formatting my check_dt to above proc format...is not doing anything to my output! Can anyone help? Thanks.
SAS Super FREQ
Posts: 8,869

Re: Format date in proc tabulate using proc format

Hi:
Assuming that check_dt is a CLASS variable, try moving the format out of the TABLE statement and into a FORMAT statement:
[pre]
format check_dt nadate.; [/pre]

When you apply the format in the TABLE statement -- you are applying it to the calculated statistics -- the ones that result from the various crossings -- not to the CLASS variables themselves.

cynthia
Occasional Contributor
Posts: 16

Re: Format date in proc tabulate using proc format

THANK YOU CYNTHIA. Your suggestion worked. Many thanks again.
Occasional Contributor
Posts: 16

Re: Format date in proc tabulate using proc format

One last question. Currently I have, proc sort by descending CHECK_Dt & then proc tabulate steps. My output has following pattern from top to bottom for the check_dt: n/a, Jan2009, Dec2008,Nov2008 & so on down to back 3 years. Is there a way to have my n/a rows to be moved to the bottom of this column, but KEEP my true dates in descending order? So it would be: Jan2009, Dec2008,Nov2008 .....n/a
Thanks
Respected Advisor
Posts: 3,799

Re: Format date in proc tabulate using proc format

I would change year = 9999 to missing then use MISSING DESCENDING on class statement.

[pre]
data test;
input id:$2. date:monyy.;
if year(date) eq 9999 then date = .;
format date monyy.;
cards;
01 sep9999
01 nov2008
02 sep2008
02 sep2007
;;;;
run;
proc print;
run;
proc format;
value nadate . = 'N/A' other = [monyy7.];
run;
proc tabulate;
class date / missing descending;
tables date,all;
format date nadate.;
run;
[/pre]
Occasional Contributor
Posts: 16

Re: Format date in proc tabulate using proc format

thank you!!!! Your suggestion worked well on my project. Greatly appreciated.
Ask a Question
Discussion stats
  • 5 replies
  • 162 views
  • 0 likes
  • 3 in conversation