Thank you for your solution Toby--and thank you as well Howard. The macro solution is much faster in my case--the difference between getting a cup of coffee after a submit as opposed to ordering a pizza. This is mostly due to the number of variables and the machine it is running on. It works perfectly my only issue now is that even though I'm excluding the category, it still shows up since I'm also using a preloadfmt+printmiss combination to make sure I get all the other categories I need. Looks like I'm going to have to look at my problem differently. The data step solution may end up being the best choice since I can create a new format just minus the "Other" category and use that to format the variable. I'm going to give it a try and I'll post again if I get stuck. Any suggestions would be welcome in the meantime.

Thanks

David Neal

toby dunn wrote: > David , > > Since your creating a macro try a macro solution: > > Proc Format ; > value AgeFMT > Low - 12 , 20 - High = 'Other' > Other = 'Teen ' > ; > > Value $SexFmt > 'F' = 'Female' > 'M' = 'Male ' > ; > Run ; > > > %Macro GetFMT( DataIn = , Var = ) ; > %Local Dsid VarNum VarFMT Close ; > > %Let Dsid = %Sysfunc( Open ( &DataIn , IS ) ) ; > > %Let VarNum = %SysFunc( VarNum( &DSID , &Var ) ) ; > %Let VarFmt = %Sysfunc( VarFmt( &Dsid , &VarNum ) ) ; > > %Let Close = %Sysfunc( Close( &dsid ) ) ; > > &VarFmt > > %Mend GetFMT ; > > > > Data MyData ; > Format Age AgeFmt. > Sex $SexFmt. ; > Set SASHELP.Class ; > OldAge = Age ; > Run ; > > Proc Print > Data = MyData ; > Where Put( Age , %GetFmt( DataIn = MyData , Var = Age ) ) = 'Teen' ; > Run ; > > > Proc Print > Data = MyData ; > Where Put( Sex , %GetFmt( DataIn = MyData , Var = Sex ) ) = 'Male' ; > Run ; > > > > Toby Dunn > > 'I dont know what she said but I loved the way she said it' > > > > > > > From: "Howard Schreier <hs AT dc-sug DOT org>" <nospam@HOWLES.COM> > Reply-To: "Howard Schreier <hs AT dc-sug DOT org>" <nospam@HOWLES.COM> > To: SAS-L@LISTSERV.UGA.EDU > Subject: Re: using formatted values for a where clause > Date: Thu, 20 Jul 2006 22:55:20 -0400 > > On Thu, 20 Jul 2006 15:01:11 -0800, David Neal <afdbn@UAA.ALASKA.EDU> > wrote: > > >I'm writing a macro to produce a set of tables, one table for each of > >several variables, using proc tabulate. Each of the variables has been > >formatted using different formats. Almost all of the format statements > >have a category "Other" which I would like to exclude from the tables. > >Unfortunately, the actual values that are formatted to the "Other" > >category vary by variable. Is there a way to do this without a separate > >data step? I'm thinking of something like: > > > >PROC TABULATE DATA=......; > >CLASS &var; > >TABLE &var,N; > >WHERE PUT(&var,&format) ne "Other"; > >RUN; > > > >But, even if this did work, I'd need to pass the format name for each > >variable. Is there a way to use the formatted value of a variable to > >exclude cases inside of proc tabulate? > > > >David Neal > > Good news: Version 9 has a VVALUE function, which returms formatted > values. > > Bad news: It works only in the DATA step, not in WHERE filters. > > So you have to introduce a view to mediate, as in: > > proc format; > value age low-12, 20-high= 'Other' > other = 'Teen' > ; > run; > > data class; > set sashelp.class; > format age age.; > run; > > data no_other; > set class; > if vvalue(age) ne 'Other'; > run; > > proc tabulate data=no_other; > class age; > table age; > run;