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
...
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.
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,
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.
@braam wrote:
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.
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.