LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous messageNext messagePrevious in topicNext in topicPrevious by same authorNext by same authorPrevious page (December 2008, week 5)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
Date:         Wed, 31 Dec 2008 10:33:06 -0800
Reply-To:     jfh@stanfordalumni.org
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Jack Hamilton <jfh@STANFORDALUMNI.ORG>
Subject:      Re: Output the Results of a Proc Freq To A SAS Dataset
In-Reply-To:  <30648bb30812301922j6ef0aafex1832f0f13a4ca25e@mail.gmail.com>
Content-Type: text/plain; charset="ISO-8859-1"

I haven't been following this discussion closely, so I may be overlooking some important aspect of the problem, but why not just use PROC TABULATE to get one output data set containing all the crosstabs?

===== ods _all_ close; proc tabulate data=abc missing out=all_out; class plan cnt h_cmc clm_dx_1_cd clm_dx_2_cd h_ldlc_scrn h_ldlc_lt100; table plan * cnt * n; table plan * h_cmc * n; table plan * h_cmc * clm_dx_1_cd * n; table plan * h_cmc * clm_dx_2_cd * n; table plan * H_LDLC_SCRN * H_LDLC_LT100 * n; run; ods listing; =====

You can use the _TYPE_ variable to figure out which rows go with which table statement.

On Tue, 30 Dec 2008 22:22:01 -0500, "Ian Whitlock" <iw1sas@GMAIL.COM> said: > Summary: ODS or OUT= for multi freqs? (with code for OUT=) > #iw-value=1 > > In response to my ranting (APOLOGIES to Mary) Andrew wrote in part: > > I also think we need to focus on a solution to the problem posed in > Gina's original post. > > ODS may offer something of a solution here. It's not perfect, but > it may save some time and steps versus a completely manual > approach. > > with the code snippet > > ods listing close; > ods output crosstabfreqs(persist=proc match_all) =table; > proc freq data=elec; > by region; > tables office*(cesched serial trans); > run; > ods listing; > > producing data sets TABLE and TABLE1-TABLE30. (I feel kind of dirty > when I have to work with the oddball TABLE and wonder at the > programmer too lazy to either have TABLE0 or go to TABLE31. > Moreover, it is a nightmare when you work interactively because the > second time you execute you get TABLE32-TABLE62 and have to remember > which is which.) > > To the point of combining the sets Andrew remarks: > > We now have 30 separate SAS data sets, each of which contains the > PROC FREQ-generated output from a cross-tabulation we requested. > These tables are created with a minimum of coding and if the number > of BY or TABLES statement variables changes, we don't have to do > very much to obtain all the tables we want after adding a variable > or two to the PROC FREQ "step," as our project needs change. > > The difficulty is "lining up" which data set is associated with > which requested cross-tab. Unfortunately, the programmer will have > to figure that out by, most likely, using PROC EYEBALL to look at > each one. Additional data step programming may be necessary to > modify and then append the tables together, if need be. But, at > the very least, we have all the PROC FREQ-generated tables in SAS > data sets for subsequent use. > > Yes, this is the crux of the problem - easy to make data sets, a > nightmare to combine them. Andrew's solution leaves the problem to > the end. Since I have put recent thought into the problem I decided > to push it to a complete general solution (Mary left out cross tabs > and Andrew left out one way freqs, and both omitted the hard part.) > > The basic problem is that PROC FREQ was never designed to carry all > the information needed to combine tables. ODS makes the classic > mistake of burying information in the variable names instead of data. > So the question is where and when to get the combining information. I > decided to make it a user's problem, like Andrew, but to put it up > front where a macro can make use of it. Moreover, I decided to make a > macro for standardizing one OUT= data set at a time, so the code is > compromise between what could be done if a managing macro collected > required information from the user and used system information where > possible to set up and run the PROC FREQ. > > To test the code I returned to the original specification with made up > data. > > proc format ; > value yn 0 = "NO" 1 = "YES" ; > run ; > > data abc ; > do plan = 1 to 3 ; > do obs = 1 to 20 * ranuni(123) ; > cnt = round ( ranuni(123) ) ; > h_cmc = round(ranuni(123)) ; > clm_dx_1_cd = round ( ranuni(123) ) ; > clm_dx_2_cd = round ( ranuni(123) ) ; > H_LDLC_SCRN = round ( ranuni(123) ) ; > H_LDLC_LT100 = substr("ABCDEF", 3*round(ranuni(123))+1, 3) ; > output ; > end ; > end ; > format hmc YN. ; > run ; > > proc freq data=abc ; > by plan; > table cnt / out=f1 noprint missing ; > table h_cmc / out=f2 noprint missing ; > table h_cmc*clm_dx_1_cd / out=f3 noprint missing ; > table h_cmc*clm_dx_2_cd / out=f4 noprint missing ; > table H_LDLC_SCRN*H_LDLC_LT100 / out=f5 noprint missing ; > run ; > > Note that both character and numeric data with formats have been > considered. The 5 tables are standardized with > > %standard_freq(data = f1, tablenum = 1 > , vars = plan cnt > , fmts = best12. best6. > ) > %standard_freq(data = f2, tablenum = 2 > , vars = plan h_cmc > , fmts = best12. yn6. > ) > %standard_freq(data = f3, tablenum = 3 > , vars = plan h_cmc clm_dx_1_cd > , fmts = best12. yn6. best8. > ) > %standard_freq(data = f4, tablenum = 4 > , vars = plan h_cmc clm_dx_2_cd > , fmts = best12. yn6. best8. > ) > %standard_freq(data = f5, tablenum = 5 > , vars = plan H_LDLC_SCRN H_LDLC_LT100 > , fmts = best12. best6. $char8. > ) > > Moreover, with this set up it is easy to insure, by eyeball that each > column of values has a consistent width. The code is repetitive, but > it could be collected by parsing or generating the PROC FREQ and using > PROC CONTENTS information for data set ABC. > > The advantage of having this structure is that it doesn't care where > or when the freq output sets were created, and it doesn't preclude > writing a managing macro to make the calls to STANDARD_FREQ. It would > be interesting to see the corresponding macro for the ODS data sets. > The one advantage that ODS has is that there is more information. On > the other hand, for me the cell counts are everything. > > Here is the macro - surprisingly short and simple once one puts the > burden on the user to write the calls. > > %macro standard_freq ( data = f3, outsuffix = _c > , tablenum = 1 > , vars = plan h_cmc clm_dx_1_cd > , fmts = best12. $char12. best12. > ) ; > %local vcnt out; > %let vcnt = %sysfunc(countw(&vars)) ; > %let out = &data&outsuffix ; > > data &out ( keep = name: v: count ) ; > retain tablenum &tablenum ; > set &data ; > %do i = 1 %to &vcnt ; > name&i = "%scan(&vars,&i)" ; > v&i = put(%scan(&vars,&i),%scan(&fmts,&i,%str( )) -L) ; > %end ; > run ; > %mend standard_freq ; > > -- > Ian Whitlock

-- Jack Hamilton Sacramento, California jfh@alumni.stanford.org <== Use this, not jfh @ stanfordalumni.org


Back to: Top of message | Previous page | Main SAS-L page