topic How to stack rows in a report in SAS Programming

How to stack rows in a report

Olga3 — Wed, 07 Oct 2020 15:29:41 GMT

Hi All,

I need to produce a report where variables have several categories. It looks like this:

Variable	Category	Value (counts)
Residence	Urban	11
	Rural	10
Education	None	10
	Primary	6
	Secondary	2
	Tertuary	3

So I need to stack rows for Residence, Education, etc. For example, for Residence, the Variable column will have one stacked row, while Categories and Values will have two rows (urban and rural). For Education, the Variable column will also have one stacked row, while Categories and Values will have four rows. I know that you can stack columns in PROC TEMPLATE, but I need to stack rows. I can produce a SAS dataset that has this structure, but I wonder if there is a more efficient way to do it - using PROC TEMPLATE or PROC REPORT. Many thanks for your help!

Best,

-Olga

Re: How to stack rows in a report

ballardw — Wed, 07 Oct 2020 15:16:52 GMT

Please look closely at the way that your "example" appears and see if that is what you actually want. I suspect that the forum might have moved somethings from the second column to the first.

If not, that is a very hard to read layout.

If the columns were shifted for some values then please show the expected layout. Best would be to provide some actual data and the layout of the expected result using that data.

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 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.

Re: How to stack rows in a report

Olga3 — Wed, 07 Oct 2020 15:30:46 GMT

Thank you ballardw. I corrected my example.

Re: How to stack rows in a report

ballardw — Wed, 07 Oct 2020 16:50:55 GMT

Are all of the variables of interest character or do you have a mix of character and numeric? There are some approaches that work with one type of data that don't work with mixed data.

Here is one way with only character data which reshapes the data.

data need;
   set sashelp.shoes;
   array v region product;
   /* length for name based on longest name of variables
      value based on longest length of any of the variables
   */
   length name $ 20 value $15;
   do i = 1 to dim(V);
      name = vname(v[i]);
      value = v[i];
      output;
   end;
   keep name value;
run;

proc tabulate data=need;
   class name value;
   table name=''*value='',
         n='Count'
   ;
run;

Re: How to stack rows in a report

Olga3 — Mon, 12 Oct 2020 17:36:23 GMT

Thank you! I already have a code that produces a SAS dataset that is shaped the way I need the output to be. What I asked about was if there is a way to display the results without extra coding. For stacked columns, proc report and proc tabulate have this option. But I haven't found a similar option for rows.