topic Re: Add computed column under across variable in SAS Procedures

Add computed column under across variable

Dreamer — Wed, 29 Apr 2015 19:10:24 GMT

Hi,

This is my sample data

data sales;

input zone $ sub_zone $ sale_month $ product $ sales;

datalines;

East East1 201501 Product1 100

East East2 201501 Product1 350

East East1 201502 Product1 200

East East2 201502 Product1 200

West West1 201501 Product1 100

West West2 201501 Product1 550

West West1 201502 Product1 150

West West2 201502 Product1 200

East East1 201501 Product2 350

East East2 201501 Product2 250

East East1 201502 Product2 300

East East2 201502 Product2 200

West West1 201501 Product2 200

West West2 201501 Product2 250

West West1 201502 Product2 300

West West2 201502 Product2 400

;

run;

and I'm trying to create report like below:

With below code I'm able to create this report except the "Max" column which is the max of values of Product sale.

Proc report data=sales missing headline;

column zone sub_zone sale_month,product,sales;

define zone / group width= 10 'Zone';

define sub_zone / group width= 10 'Sub-Zone';

define sale_month / across '';

define product / across '';

define sales / analysis sum format=4.0 '';

run;

quit;

Can any one please guide me on how to add a computed column under across variable?

Thanks in advance!!

Re: Add computed column under across variable

Steelers_In_DC — Wed, 29 Apr 2015 19:33:09 GMT

This isn't exact but it's close, see if this works or if you can manipulate it for your needs. I'm getting the max before the proc report:

data have;

input zone $ sub_zone $ sale_month $ product $ sales;

datalines;

East East1 201501 Product1 100

East East2 201501 Product1 350

East East1 201502 Product1 200

East East2 201502 Product1 200

West West1 201501 Product1 100

West West2 201501 Product1 550

West West1 201502 Product1 150

West West2 201502 Product1 200

East East1 201501 Product2 350

East East2 201501 Product2 250

East East1 201502 Product2 300

East East2 201502 Product2 200

West West1 201501 Product2 200

West West2 201501 Product2 250

West West1 201502 Product2 300

West West2 201502 Product2 400

;

proc sql;

create table want as

select *,max(sales) as max

from have

group by zone,sub_zone,sale_month;

Proc report data=want missing headline;

column zone sub_zone sale_month,product,sales sale_month,product,max;

define zone / group width= 10 'Zone';

define sub_zone / group width= 10 'Sub-Zone';

define sale_month / across '';

define product / across '';

define sales / analysis sum format=4.0 '';

define max / group width= 3 'Max';

Re: Add computed column under across variable

Cynthia_sas — Wed, 29 Apr 2015 22:44:17 GMT

Hi:

First, you don't need a computed column or an SQL step. You can use PROC REPORT to do everything through the use of ALIASES. SALES can be used for the SUM statistic and can also be used for the MEAN statistic, as shown below.

cynthia

Re: Add computed column under across variable

Dreamer — Thu, 30 Apr 2015 13:13:19 GMT

Thanks a lot that's solve my problem.

But I have a question here.

Why have you used (Sales=smax) in column statement?

Also can you please recommend me some reading material on SAS Reporting. Creating SAS reports are a big part of my work!!