topic two columns in dataset to two rows in PROC REPORT? in SAS Procedures

two columns in dataset to two rows in PROC REPORT?

braam — Fri, 24 Jan 2020 13:35:12 GMT

I have a panel data, which I would like to display by using PROC REPORT.


proc report data=sashelp.stocks
 out=lat(drop=_break_) nowd;
 where year(date)>= 2005;
 
column Stock Date, (AdjClose Volume);
define Stock / group left;
define Date/ across order= internal;

COMPUTE AdjClose ;
	CALL DEFINE(_COL_, "style", "STYLE=[BACKGROUND=MISTYROSE]");
	ENDCOMP; 

COMPUTE Volume;
	CALL DEFINE(_COL_, "style", "STYLE=[BACKGROUND=lightblue]");
	ENDCOMP; 
quit;

	Date
	03JAN05		01FEB05		01MAR05		01APR05		02MAY05		01JUN05		01JUL05		01AUG05		01SEP05		03OCT05		01NOV05		01DEC05
Stock	AdjClose	Volume	AdjClose	Volume	AdjClose	Volume	AdjClose	Volume	AdjClose	Volume	AdjClose	Volume	AdjClose	Volume	AdjClose	Volume	AdjClose	Volume	AdjClose	Volume	AdjClose	Volume	AdjClose	Volume
IBM	$91.62	5,960,945	$90.97	4,455,657	$89.79	5,025,627	$75.05	10,709,200	$74.43	6,896,904	$73.10	6,439,536	$82.23	8,056,590	$79.62	4,801,386	$79.22	5,772,280	$80.86	7,019,666	$88.01	5,556,471	$81.37	5,976,252
Intel	$21.81	82,481,585	$23.39	78,924,846	$22.65	63,823,945	$22.93	67,651,157	$26.38	64,584,867	$25.46	55,191,463	$26.55	55,455,755	$25.24	45,049,221	$24.19	53,493,957	$23.06	58,262,005	$26.27	56,234,966	$24.57	54,107,733
Microsoft	$25.77	79,642,818	$24.75	75,992,316	$23.78	72,899,708	$24.89	77,090,234	$25.46	62,699,752	$24.52	62,956,713	$25.28	69,046,604	$27.10	65,529,978	$25.47	66,976,476	$25.44	72,132,475	$27.48	71,469,194	$25.96	62,892,384

I was wondering if I can make two "rows" (one for AdjClose and the other for Volume) for each group (i.e., IBM, Intel, Microsoft).

What I would like to have:

03JAN05 01FEB05 ......

IBM AdjClose

Volume

Intel AdjClose

Volume

Microsoft AdjClose

Volume

Re: two columns in dataset to two rows in PROC REPORT?

PaigeMiller — Fri, 24 Jan 2020 13:56:19 GMT

While this is possible, it requires you to re-arrange the data set (extra work) and then you'd also have to provide different formats for the different rows, as AdjClose and Volume would have to formatted differently (extra work).

Is it worth the extra work??

Re: two columns in dataset to two rows in PROC REPORT?

ballardw — Fri, 24 Jan 2020 18:15:19 GMT

Tabulate will do this pretty easily:

proc tabulate data=sashelp.stocks;
   where year(date)>= 2005;
   class stock date;
   var AdjClose  volume;
   tables stock*( AdjClose*max=''*f=dollar8.2  volume*max=''*f=comma14.),
          date
          /row=float
   ;
run;

A concern though is tabulate must report a statistic for the body of the table as it has no "list" behavior except for the row and column headings. So if you have a single value per group crossing (stock and date), such as in this case, you could use any of Mean, Max, or Min. The =' ' is to suppress a column or row label, the format has to be applied to the Statistic not the variable. The row=float table option suppresses a blank column you would get for the missing Max label. Style elements for background could immediately follow the statistic to display as desired (but before the *f= which sets the specific format for the cells).

Re: two columns in dataset to two rows in PROC REPORT?

braam — Fri, 24 Jan 2020 21:25:07 GMT

@PaigeMiller Yes, I would like to try if there is such a way. Could you please share your insights with me?

@ballardw Thanks. It actually worked for me. But an issue I encountered is that I cannot use character variables, unfortunately.

Re: two columns in dataset to two rows in PROC REPORT?

PaigeMiller — Sat, 25 Jan 2020 12:14:44 GMT

Step 1: rearrange the data so that the two columns of interest are now one column with two rows.

data stocks;
    set sashelp.stocks;
    length type $ 8;
    type='AdjClose';
    value=adjclose;
    output;
    type='Volume';
    value=volume;
    output;
run;

Step 2: Execute PROC REPORT with the new "one column with two variables" as a GROUP variable

proc report data=stocks(where=(year(date)>= 2005));
    columns stock type date,value;
    define stock/group;
    define type/group;
    define date/across;
    define value/sum;
run;

Step 3: Change the formats on the rows if desired, see this explanation by @Cynthia_sas here: https://communities.sas.com/t5/ODS-and-Base-Reporting/PROC-REPORT-different-formats-for-different-rows/m-p/403183#M19580

Re: two columns in dataset to two rows in PROC REPORT?

Cynthia_sas — Sat, 25 Jan 2020 14:47:32 GMT

Hi:

Using the example of restructured data previously posted, here's the way to change the style and format for the different rows, based on the value of the TYPE variable:

Hope this helps.

Cynthia