Regarding Proc tabulate

[JasonNC]

Hi,

I have a scenario i want to do grouping in two different ways and get the result in one table.

  PROC TABULATE DATA=TEST

          OUT = TOTAL ( WHERE=(TOTAL_SUM NE .) RENAME=(AMT_SUM=TOTAL_SUM) DROP=_TABLE_ _PAGE_ _TYPE_ );

          CLASS  STATE CITY COUNTY  ;

          VAR AMT;

          TABLE STATE   * (CITY ALL)  , AMT *SUM  /printmiss;

  TABLE STATE * (CITY * COUNTY ALL) , AMT *SUM  /printmiss;

RUN;

In the result table Total what i want to have is grouping the amount on STATE and CITY and Later on STATE ,CITY and COUNTY.but i am not getting the right result.

Can you let me know wether any changes to the approach

[Cynthia_sas]

Hi:

  I'm not sure what you want from TABULATE. You are dropping all the variables that would give you enough information to find the rows with the breakdown you want. For example, you should see a value for _TABLE_=1 for the data from first TABLE statement and a value for _TABLE_=2 for the output from the second TABLE statement. In addition, the values of _TYPE_ will tell you which CLASS variables contributed to the statistic. The output dataset that you create with OUT= will not have the same structure as the TABULATE output from ODS LISTING, ODS HTML or ODS PDF, for example.

  I expect that if you examine _TABLE_, you will see the rows that are coming from the TABLE statement for STATE*CITY*COUNTY. So, I'd suggest that you keep _TYPE_ and _TABLE_ and examine the output. If what you want is the structured table, then I'd suggest you use ODS to capture the TABULATE output directly, as a report, instead of an output dataset.

  Can you explain the structure of what you do want compared to what you're seeing (after you examine _TABLE_ and _TYPE_.

cynthia

[JasonNC]

Hi Cynthia,

My main intention was to calCulate the sum in proc tabulate instead of using PROC SQL.

The calculations i want are

1)Total sum for each county

2)total sum for each county there whith an indicator 'G'

3)perecentage of county with g in the total sum for each county

4)total sums for each state

5)total sums for each state with an indciator g.

6)total percentage for state.

I am using Proc Sql for calculations.

when i use PROC SQL it takes some steps for calculations as i have to use different groups.

So i am thinking of a method where i can do the calculations in less number of steps.

if you have any questions let me know.

[Cynthia_sas]

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;