topic Re: Format date in proc tabulate using proc format in ODS and Base Reporting

Format date in proc tabulate using proc format

Karo_22 — Thu, 22 Jan 2009 18:15:00 GMT

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.

Re: Format date in proc tabulate using proc format

Cynthia_sas — Fri, 23 Jan 2009 03:31:14 GMT

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

Re: Format date in proc tabulate using proc format

Karo_22 — Fri, 23 Jan 2009 12:31:12 GMT

THANK YOU CYNTHIA. Your suggestion worked. Many thanks again.

Re: Format date in proc tabulate using proc format

Karo_22 — Fri, 23 Jan 2009 13:01:28 GMT

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

Re: Format date in proc tabulate using proc format

data_null__ — Fri, 23 Jan 2009 13:36:13 GMT

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]

Re: Format date in proc tabulate using proc format

Karo_22 — Fri, 23 Jan 2009 14:32:23 GMT

thank you!!!! Your suggestion worked well on my project. Greatly appreciated.