BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Dreamer
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ

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

3 REPLIES 3
Steelers_In_DC
Barite | Level 11

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

Cynthia_sas
SAS Super FREQ

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

Dreamer
Obsidian | Level 7

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

SAS Innovate 2025: Call for Content

Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!

Submit your idea!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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