Hi: You still did not explain what is wrong with what's being created by TABULATE. If you were hoping to get a percentage, nothing in your original code used the PCTSUM statistic, so you won't get a percentage without PCTSUM. There's nothing in TABULATE that will allow you to test for a value (such as a "G indicator") but in my example below, I made some fake data from SASHELP.CLASS and I made 2 versions of a variable for those observations where NAME/COUNTY begins with 'J'. In my fake data, AMT= HEIGHT*WEIGHT -- so when the name begins with J, my J_IND variables are created. STATE is based on the value of the SEX variable and CITY is based on a user-defined format. I only put 3 cities in each STATE so I'd have fairly small tables. If you examine the output -- you will see that the program produces 3 formatted PROC TABULATE tables. Then, the WORK.TABOUT data set has a lot of observations. Many of the statistics seem to be repeated values. The _TABLE_ and _TYPE_ variables help you figure out which observations you care about. For example, when _TABLE_=1, the _TYPE_=000 represents the total of AMT and the _TYPE_=100 represents the breakdown by STATE. By carefully examining the values for _TABLE_ and _TYPE_, you will find the breakdowns you need. I think that the last TABLE statement (#3 will probably give you all the info you want -- but the structure of the output dataset will be in rows and columns -- it will not look at all like the TABULATE report structure. cynthia proc format; value age2F 11,12='Boca Raton' 13,14='Lakeland' 15,16='Orlando'; value age2M 11,12='Boston' 13,14='Nantucket' 15,16='Salem'; run; ** make some data; data fakedata; length city $15; set sashelp.class; amt = height*weight; ** based on some condition, capture value into sep variable; ** note that j_ind is missing and j_ind2 is 0; ** this will impact the COUNT of observations; j_ind = .; j_ind2 = 0; if substr(name,1,1) = 'J' then do; j_ind=amt; j_ind2=amt; end; if sex = 'M' then do; state = 'MA'; city = put(age,age2M.); end; else if sex = 'F' then do; state='FL'; city = put(age,age2F.); end; county=name; run; ods listing close; ods html file='c:\temp\test_tabulate.html'; options nocenter; PROC TABULATE DATA=fakedata out=work.tabout; title 'TABULATE structured tables'; CLASS state city county; VAR amt j_ind j_ind2; TABLE STATE all, amt *(n SUM*f=comma16. pctsum) j_ind*(n sum*f=comma16. pctsum) j_ind2*(n sum*f=comma16. pctsum) / box='state'; TABLE STATE * (city ALL) all , amt *(n SUM*f=comma16. pctsum) j_ind*(n sum*f=comma16. pctsum) j_ind2*(n sum*f=comma16. pctsum) / box='state and city'; TABLE STATE * (city * county ALL) all, amt *(n SUM*f=comma16. pctsum) j_ind*(n sum*f=comma16. pctsum) j_ind2*(n sum*f=comma16. pctsum) / box='state, city and county'; RUN; proc print data=tabout ; by _table_; var state city county _TABLE_ _TYPE_ _PAGE_ amt_N amt_Sum amt_PctSum_000 j_ind_N j_ind_Sum j_ind_PctSum_000 j_ind2_N j_ind2_Sum j_ind2_PctSum_000 ; title 'Output Data Set from TABULATE'; label _table_ = 'From TABLE Statement'; run; ods html close; title;
... View more
