[PROC REPORT] Two variables in a column

[braam]

Hi all,

My data is a panel set with multiple variables. I would like to see my panel data for an inspection purpose, so I'm wondering if I can display two variables under a column using proc report. Let me take an example with the below code:

*Panel Data with two variables;
proc report data= sashelp.pricedata;
	columns productline date, (sale price);
	define productline / group;
	define date / across;
	run;

	date
	APR98		AUG98		DEC98		FEB98		JAN98		JUL98		JUN98		MAR98		MAY98		NOV98		OCT98		SEP98
productLine	sale	price	sale	price	sale	price	sale	price	sale	price	sale	price	sale	price	sale	price	sale	price	sale	price	sale	price	sale	price
Line1	1176	195.69	1090	200.7	1101	200.7	1086	200.7	1028	200.7	1145	200.7	1263	189.2	1132	200.7	1332	192.855	1068	200.7	1088	200.7	1073	200.7
Line2	1690	194.5	1728	187.71	1622	194.5	1631	194.5	1819	184.78	1816	194.5	1850	194.5	1645	194.5	1674	194.5	1538	194.5	1843	186.1	1789	189.1
Line3	1549	352.5	1532	346.6	1491	352.5	1539	352.5	1434	352.5	1600	342.72	1621	352.5	1814	326.825	1593	352.5	1424	352.5	1461	352.5	1557	352.5
Line4	1784	434.25	1592	442.2	1492	442.2	1729	442.2	1617	442.2	1742	423.9	1720	442.2	1719	442.2	1729	442.2	1628	424.17	1491	442.2	1632	420.15
Line5	890	163.5	871	163.5	823	163.5	868	163.5	850	151.05	889	163.5	902	163.5	849	163.5	900	163.5	753	163.5	812	163.5	916	151.425

The above table shows a panel data with two variables (price and sale), but it doesn't look so pretty to me. Rather I would like to display "sale" and "price" in a column using two rows for each product line. In other words, the five-row table should be transformed to a ten-row table. Could anybody help me with this? Thanks!

Line1 Sale 1176

          Price 195.69

Line2 Sale 1690

          Price 194.5

...

[ballardw]

Proc report doesn't like stacking variables in a single column. But Proc Tabulate allows this:

proc tabulate data=sashelp.pricedata;
   class productline date;
   var sale price;
   table productline*(sale price)*sum=' ',
         date
         /row=float
   ;
run;

the sum=' ' suppresses the default statistic label that would appear without the statistic. The /row=float table option suppresses the column that Sum label would appear in.

Caution for beginning users of Proc Tabulate: by default any record with a missing value of any of the class variables will be dropped from the report.

[ed_sas_member]

Hi @braam 

PROC TABULATE suggested by @ballardw is definitely the most flexible and 'straightforward' approach with minimal code. Nevertheless, if you want to stick to PROC REPORT for further enhancement for example, you can perform some data transposition beforehand:

proc sort data=sashelp.pricedata out=pricedata;
	by productline date;
run;

proc means data=pricedata nway noprint;
	var sale price;
	by productline date;
	output out=pricedata_sum (drop=_:) sum=sale price;
run;

proc transpose data=pricedata_sum out=pricedata_tr (drop=_: rename=(col1 = value)) name=analysis_var;
	var sale price;
	by productline date;
run;

proc report data= pricedata_tr;
	columns productline analysis_var date, value;
	define productline / group '';
	define analysis_var / group '' descending;
	define date / across '' order=data;
	define value / analysis '';
run;

Best,

[braam]

@ballardw @ed_sas_member 

Thanks for your answers! They work very well. Relatedly, can I change format of numeric variables in proc tabulate? I would like to display numbers rounded up to integers.

[ballardw]

---

@braam wrote:

@ballardw @ed_sas_member 

Thanks for your answers! They work very well. Relatedly, can I change format of numeric variables in proc tabulate? I would like to display numbers rounded up to integers.

---

You set specific formats for a statistic by adding *f=<format name> after the statistic and/or label modifier.

Example: Variablename *sum=' '*f=f5.0  would have the sum of the variable displayed as an up to 5 digit integer.

You can use your custom build formats here as well if any.