Help using Base SAS procedures

Add computed column under across variable

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 98
Accepted Solution

Add computed column under across variable

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!!


Accepted Solutions
Solution
‎04-29-2015 06:44 PM
SAS Super FREQ
Posts: 8,864

Re: Add computed column under across variable

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

no_sql_required.png

View solution in original post


All Replies
Valued Guide
Posts: 860

Re: Add computed column under across variable

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';

Solution
‎04-29-2015 06:44 PM
SAS Super FREQ
Posts: 8,864

Re: Add computed column under across variable

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

no_sql_required.png

Frequent Contributor
Posts: 98

Re: Add computed column under across variable

Posted in reply to Cynthia_sas

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!!

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 274 views
  • 3 likes
  • 3 in conversation