topic Re: proc report with across in SAS Programming

proc report with across

Ronein — Tue, 17 Nov 2020 12:25:25 GMT

Hello

I am trying to create a summary report that will contain following columns:

Team categories

Sum of Y for source 2009

Sum of Y for source 1909

Number of customers in 2009

Number of customer in 1909

pct number of customers 2009 (from total)

pct number of customers 1909 (from total)

pct sum of y 2009 (from total)
pct sum of y 1909 (from total)

The error I receive is

ERROR: There is more than one ANALYSIS usage associated with
the column defined by the following elements.
Name Usage
-------------------------------- --------
source ANALYSIS
Y ANALYSIS
n STATISTIC


Data ttt;
input ID team $ source Y;
cards;
1 a 2009 10
2 a 2009 15
3 b 2009 20
4 a 2009 25
5 a 2009 30
6 b 2009 18
1 b 1909 22
3 b 1909 32
4 a 1909 50
6 a 1909 5
;
run;


proc report data=ttt nowd out=wanted style(summary)=Header;
column team ( source,(Y, (n sum  pctn pctsum)));
define team / group style(column)=Header;
define Y/sum;
define n/ 'Nr_Customers' f=comma28.;
define Y/" " analysis sum;
define sum / "Revenue" f=comma28.;
define pctn / 'PCT_Customers' f=percent9.2;
define pctsum/ 'PCT_Y' f=percent9.2;
rbreak after /summarize  style (summary)=Header;
compute after;
endcomp;
Run;

Re: proc report with across

PaigeMiller — Tue, 17 Nov 2020 12:53:07 GMT

SOURCE is an across variable, but it doesn't appear in a DEFINE statement.

Try this:

proc report data=ttt nowd out=wanted style(summary)=Header;
column team  source,Y, (n sum  pctn pctsum);
define team / group style(column)=Header;
define source/across;
define n/ 'Nr_Customers' f=comma28.;
define Y/" " analysis sum;
define sum / "Revenue" f=comma28.;
define pctn / 'PCT_Customers' f=percent9.2;
define pctsum/ 'PCT_Y' f=percent9.2;
rbreak after /summarize  style (summary)=Header;
compute after;
endcomp;
run;

If I may be so bold as to make a suggestion on improvement, the value in the label in the DEFINE statement doesn't have to follow SAS variable naming rules. You could use actual readable English words/phrases, such as

define n/ 'Number of Customers' f=comma28.;
define pctsum/'Percent of Revenue' f=percent9.2;

Re: proc report with across

Ronein — Tue, 17 Nov 2020 13:29:37 GMT

Great!!!
One more question.
If I want to have another order of columns that for each statistics will see a pair of columns ,what will be the way to do it?
So the order of columns will be
Team

Sum of Y for source 2009
Sum of Y for source 1909

Number of customers in 2009
Number of customer in 1909

pct number of customers 2009 (from total)
pct number of customers 1909 (from total)

pct sum of y 2009 (from total)
pct sum of y 1909 (from total)

Re: proc report with across

PaigeMiller — Tue, 17 Nov 2020 13:44:29 GMT

column team  source,Y,sum source,Y,n source,y,pctn source,y,pctsum;
define source/across descending;

Re: proc report with across

Ronein — Tue, 17 Nov 2020 13:56:17 GMT

Is it possible to add a narrow empty column with dark blue color between each pair of column? and also to do grey background for total roaw.
columns will be :
Team
Sum of Y for source 2009
Sum of Y for source 1909
empty column with dark blue background
Number of customers in 2009
Number of customer in 1909
empty column with dark blue background
pct number of customers 2009 (from total)
pct number of customers 1909 (from total)
empty column with dark blue background
pct sum of y 2009 (from total)
pct sum of y 1909 (from total)
empty column with dark blue background

Re: proc report with across

ballardw — Tue, 17 Nov 2020 15:40:37 GMT

An actual "column", in Proc report terms would mean a variable is needed.

A BORDER can be applied to any side of a cell with a style option such as in below:

proc report data=ttt nowd out=wanted style(summary)=Header;
column team  source,Y, (n sum  pctn pctsum);
define team / group style(column)=Header;
define source/across;
define n/ 'Nr_Customers' f=comma28.;
define Y/" " analysis sum;
define sum / "Revenue" f=comma28.
            style={borderleftwidth=4 borderleftcolor=blue};
define pctn / 'PCT_Customers' f=percent9.2;
define pctsum/ 'PCT_Y' f=percent9.2;
rbreak after /summarize  style (summary)=Header;
compute after;
endcomp;
run;

Depending on your ods destination you may want to specify a unit with the border width for more positive control.

You are currently telling the summary row to use the header style. This sets the background to light gray:

rbreak after /summarize  style (summary)={background=lightgray};

Any other appearance options are done with style element name = property.

Re: proc report with across

PaigeMiller — Wed, 18 Nov 2020 12:19:27 GMT

To get a column of a particular color (let's say blue), you could also use a variable, and tell PROC REPORT to display the variable text in blue and background color in blue, and so you can't see the text, it appears to be an entirely blue column.