topic Re: [PROC REPORT] Two variables in a column in SAS Procedures

[PROC REPORT] Two variables in a column

braam — Wed, 10 Jun 2020 15:12:02 GMT

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

	date
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

...

Re: [PROC REPORT] Two variables in a column

ballardw — Wed, 10 Jun 2020 15:25:43 GMT

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.

Re: [PROC REPORT] Two variables in a column

ed_sas_member — Wed, 10 Jun 2020 17:52:27 GMT

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,

Re: [PROC REPORT] Two variables in a column

braam — Wed, 10 Jun 2020 20:32:25 GMT

@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.

Re: [PROC REPORT] Two variables in a column

ballardw — Wed, 10 Jun 2020 20:55:00 GMT

@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.

	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