Proc Tabulate

[Daily1]

How to create a tabulated report based on data:

Column CATEGORY in H means a horizontal report. TITLE_LEVELS indicate how many levels of title there are. TITLE_LEVEL1 is the main header, TITLE_LEVEL2 is the sub-header of TITLE_LEVEL1, and TITLE_LEVEL3 is the sub-header of TITLE_LEVEL2.

All columns are Character data type

proc tabulate data=datset;
    class TITLE_LEVEL1 TITLE_LEVEL2 TITLE_LEVEL3 Column1  ;
    table TITLE_LEVEL1*
          TITLE_LEVEL2*TITLE_LEVEL3;
run;

i have data

i want 

PFA

[ballardw]

Many users here don't want to download Excel files because of virus potential, others have such things blocked by security software. Also if you give us Excel we have to create a SAS data set and due to the non-existent constraints on Excel data cells the result we end up with may not have variables of the same type (numeric or character) and even values.

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the </> icon or attached as text to show exactly what you have and that we can test code against.

If I understand the picture of your data, always questionable assumption, you are going to have issues with Proc Tabulate and missing values for CLASS variables. Where Title2 and Title3 are missing the observation by default will be removed from the data for the table. IF you use the option / missing to allow those observations to be in the output then MISSING will be a valid column in the output.

Since your Value of State is in the same variable as the 'No. Students' anything that groups Title2 or Title3 values would be under State as well. Since your Column1 contains TEXT it really can not appear in the body of a table generated by Proc Tabulate. The only thing in cells in the proc tabulate report are statistics: n, sum, pctn, mean, something. Not the text of another variable.

Basically, your data is not structured for simple Proc Tabulate code.

The only tool I know in SAS to handle this sort of stuff is either the Report Writing Interface in the data step (where you do have lots of control but complex coding) or a data step and ancient column/line control output which is just about the same.

With actual data I, or someone else, may have other suggestions but without serious reworking of the data  and maybe more explanation it is unlikely to be  a direct Proc Tabulate solution.

[Tom]

If you want help please share your data as data, not photographs or attached files, but simple text, preferable as a data step that can be copied and run.

[Tom]

---

@Daily1 wrote:

i want 

---

I would assume you have grouping variables for the first two columns.

For the others the first line could be a constant text or perhaps the label of a variable.  For the second and third lines you will need three variables.  One with values like BOYS and GIRLS, one with values like 1st, 2nd, 3rd and a third with the actual numeric values like 2.031.

I have no idea what the fourth line is supposed to be and how it adds anything at all to the presentation.

[Cynthia_sas]

Hi:

  I agree with @Tom -- the report row that just shows the numbers 1-8 does not make sense to me. If you restructured your data a bit, this would be much easier to generate with PROC REPORT because you can use headers that span the columns as shown in this example:

  However, I realize that you may not want to change your current process that generates an Excel file, but your current data structure is not going to work for TABULATE. Restructured data will work with REPORT as shown above and with TABULATE as shown below:

  Cynthia