topic Re: proc tabulate with all column vertically in SAS Programming

proc tabulate with all column vertically

sascode — Fri, 26 May 2023 13:57:33 GMT

Hello,

I a have a dataset as follows:

data have;
input year item $ type $ ;
datalines;
2020 a 1
2020 a 2
2020 a 1
2021 b 1
2021 b 2
2021 a 3
2022 a 1
2022 a 2
2022 a 2
2022 b 3;
run;

Using tabulate procedure as follows, I can get desired results but I wanted to "all" total items sold each year vertically , instead of horizontally. So mainly, how to rotate all row for each year from horizontally to vertically?

proc tabulate data = have;
class year item type;
table year * (item * type all), N='Count type'(pctn< type all> = "Pctn Type" ) /printmiss misstext= '0' ;
run;

Here is desired output:

			Count type	Pctn Type	All
year	item	type	2	66.67	3
2020	a	1	2	66.67
		2	1	33.33
		3	0	0
	b	1	0	0
		2	0	0
		3	0	0
2021	item	type	0	0	3
	a	1	0	0
		2	0	0
		3	1	100
	b	1	1	50
		2	1	50
		3	0	0
2022	item	type	1	33.33	4
	a	1	1	33.33
		2	2	66.67
		3	0	0
	b	1	0	0
		2	0	0
		3	1	100

Re: proc tabulate with all column vertically

ballardw — Fri, 26 May 2023 15:50:21 GMT

Tabulate isn't going to do a "merged row cells" that way.

About the only way I can think of that to make one column with merged count would be to summarize the data in a data set and use the data step Report Writing Interface build the cells with the row spanning you request.

Re: proc tabulate with all column vertically

sascode — Fri, 26 May 2023 16:30:33 GMT

OK, i understand.

Thank you .

Re: proc tabulate with all column vertically

Patrick — Sat, 27 May 2023 01:58:18 GMT

You could add the information at the bottom of the table.

proc tabulate data = have;
class year item type;
keylabel n=' ';
table 
  year * (item * type) year*all,
  N='Count type'(pctn< type all> = "Pctn Type" )
  /printmiss misstext= '0' ;
run;

Re: proc tabulate with all column vertically

sascode — Tue, 30 May 2023 19:11:08 GMT

Yes, it is true, however, i would like to have it vertically .
Thank you for your time.