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-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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
  • 943 views
  • 3 likes
  • 3 in conversation