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

Hi, my report compares one salesperson's sales performance to a team's average sales (excluding that target salesperson) performance by region and department across the last 3 years.   The part of the report I posted works fine.


What I want on a separate report is the same information, but each page will be a separate department and at the bottom, list out the individual salespersons that contributed to that average (note that this variable for everyone else [SalesPerson] is not shown on the current report).  That number of SalesPersons will vary (between 1-20) by year or department.

 

Notes on variables in proc report step:

CompareFrom = dichotomous character value where target person = "Target" and everyone else = "Team" for use in the across field

 

Compareto = dichotomous numeric value for counting where target person = 0 and everyone else = 1

 

 

 

PROC REPORT DATA=compare;

COLUMN Region Department year, (Comparefrom, Sales Salesratio Compareto);
DEFINE Region / group ;
DEFINE Department / group "Department";
DEFINE year / across ;
DEFINE Comparefrom / across;
DEFINE Sales / analysis mean;
define Salesratio /computed format = percent6.;
Define Compareto / analysis "Number in Peer Group" sum;


compute salesratio;
_c5_ = _c3_ / _c4_;
_c9_ = _c7_ / _c8_;
_c13_ = _c11_ / _c12_;
endcomp;

 

run;

 

 

 

So, ideally this new report would have a page break after each department and have the same information, and then list those salespersons who made up the comparison average underneath the comparison data.  Here is what I would like the output to look like:

 

  201320142015
  TargetTeam  TargetTeam  TargetTeam  
RegionDepartmentSalesSalesSalesRatioNumber in Peer GroupSalesSalesSalesRatioNumber in Peer GroupSalesSalesSalesRatioNumber in Peer Group
Latin AmericaHousehold80007000114%875007500100%1060001000060%5
              
              
   8   10   5  
List of Team Members  Amy Adams
Bob Brown
Chris Clark
Darcy Darwin
Edward Egghead
Fred Farkle
Greg Gage
Henry Higbee
   Chris Clark
Darcy Darwin
Fred Farkle
Iris Isis
Kirk Kline
Larry Lemon
Otis Othello
Perry Pratt
Quin Quartz
Rosa Reynolds
   Bob Brown
Edward Eggplant
Fred Farkle
Rosa Reynolds
Sara Sweeney
  

 

1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ

Hi:

  Here's the code I used to generate my report. I used only 4 ages from SASHELP.CLASS to keep the report smaller:

** make first pass through data and get names and count for each age group;
** can probably do this in SQL too or with proc means;
** wanted to show multiple steps for ease of following logic;
** do not need this first report any destination;
proc report data=sashelp.class out=classout;
  where age in (12, 13, 14, 15);
  column age name height n;
  define age / order;
  define name / order;
  define height / mean;
  define n / 'count this group';
  break after age / summarize;
run;

proc print data=classout;
run;

** make a macro variable for each agegroup and for names;
** ag12, ag13, ag14, ag15 will hold names for each group;
** cnt12, cnt13, cnt14, cnt15 will hold count for each group;
** names are separated by ESCAPECHAR+{newline};
data _null_;
  length agegrp_names $300 ;
  retain agegrp_names;
  set classout;
  by age;
  if first.age then agegrp_names=' ';
  agegrp_names = catx('~{newline 1}',agegrp_names,name);
  if last.age and upcase(_break_) = 'AGE' then do;
     call symputx('ag'||put(age,2.0),agegrp_names);
	 call symputx('cnt'||put(age,2.0),n);
  end;
  run;

  ** look for macro variables in symbol table;
%put _global_;

** sort SASHELP.CLASS by only ages wanted;
  proc sort data=sashelp.class out=subclass;
  where age in (12,13, 14, 15);
  by age;
  run;
    
  ** final report;
  ods escapechar='~';
  options nobyline;

  ods html path='c:\temp' file='page_by_list.html';
  title 'Report with Page for Age #byval1';
  proc report data=subclass;
  by age;
    column age height,sex height=ovavg;
	define age / group;
	define sex / across;
	define height / mean f=6.2 'Average';
	define ovavg / mean f=6.2 'Overall Average';
	break after age / page;
	compute before age;
	  length grpnames $300 grpcnt 8;
	  ** retrieve macro values from symbol table;
       grpnames = symget('ag'||put(age,2.0));
       grpcnt = input(symget('cnt'||put(age,2.0)),best8.);
	endcomp;
	compute after age / style=Header{just=l};
	   line 'Student Count for This Average:' grpcnt ;
	   line 'Names for this Group:';
	   line grpnames $varying300.;
	endcomp;
  run;
ods html close;
title; options byline;

Hope this helps point you in a direction.

 

Cynthia

View solution in original post

4 REPLIES 4
Cynthia_sas
SAS Super FREQ

Hi:
Without data, it is impossible to answer your question. Is it possible -- well, it depends, on the structure of your data, where the SalesPerson info comes from, if, as you say, SalesPerson is not shown/used/present on the current report.

Every person who tries to design fake data for this question might design data in the wrong structure and then tailor the report for that structure. You don't need to provide a sample of ALL your data, just 2 regions with 2 departments for each region should be enough. But without data, it's hard to make a constructive suggestion.

Also, you haven't indicated what your ODS output destination is -- so you haven't showed your ODS invocation statements or your style being used or any information like that. Is your output being sent to a paged destination, like RTF, or PDF or is your destination to HTML or Excel?

Showing your code was good, but you're asking people to guess what your input data looks like. From the fake names you've used, I would guess that you either generated this output by typing it or you might already have some fake data? Posting a program that makes the data would be useful way to show us what the WORK.COMPARE data looks like and would give an idea of where the SalesPerson names might come from.

 

  As a quick proof of concept, I did this with SASHELP.CLASS:

poc_list.png

It needed a data step and 2 passes thru the data and the use of macro variables. The first pass got the names and the count associated with each age group and then I had to build macro variables from the names.  The final step was the proc report step. I probably could have used PROC SQL and PROC REPORT too.

Cynthia

denperp
Obsidian | Level 7

Attached is sample data and the proc report that I used.  The names are in the datafile compare [SalesPerson} that I want listed at the end by region/department (but only where compareto = 1).  Output is to RTF.

 

What you did looks like what I am trying to get at.  Thank you in advance!

Cynthia_sas
SAS Super FREQ

Hi:

  Here's the code I used to generate my report. I used only 4 ages from SASHELP.CLASS to keep the report smaller:

** make first pass through data and get names and count for each age group;
** can probably do this in SQL too or with proc means;
** wanted to show multiple steps for ease of following logic;
** do not need this first report any destination;
proc report data=sashelp.class out=classout;
  where age in (12, 13, 14, 15);
  column age name height n;
  define age / order;
  define name / order;
  define height / mean;
  define n / 'count this group';
  break after age / summarize;
run;

proc print data=classout;
run;

** make a macro variable for each agegroup and for names;
** ag12, ag13, ag14, ag15 will hold names for each group;
** cnt12, cnt13, cnt14, cnt15 will hold count for each group;
** names are separated by ESCAPECHAR+{newline};
data _null_;
  length agegrp_names $300 ;
  retain agegrp_names;
  set classout;
  by age;
  if first.age then agegrp_names=' ';
  agegrp_names = catx('~{newline 1}',agegrp_names,name);
  if last.age and upcase(_break_) = 'AGE' then do;
     call symputx('ag'||put(age,2.0),agegrp_names);
	 call symputx('cnt'||put(age,2.0),n);
  end;
  run;

  ** look for macro variables in symbol table;
%put _global_;

** sort SASHELP.CLASS by only ages wanted;
  proc sort data=sashelp.class out=subclass;
  where age in (12,13, 14, 15);
  by age;
  run;
    
  ** final report;
  ods escapechar='~';
  options nobyline;

  ods html path='c:\temp' file='page_by_list.html';
  title 'Report with Page for Age #byval1';
  proc report data=subclass;
  by age;
    column age height,sex height=ovavg;
	define age / group;
	define sex / across;
	define height / mean f=6.2 'Average';
	define ovavg / mean f=6.2 'Overall Average';
	break after age / page;
	compute before age;
	  length grpnames $300 grpcnt 8;
	  ** retrieve macro values from symbol table;
       grpnames = symget('ag'||put(age,2.0));
       grpcnt = input(symget('cnt'||put(age,2.0)),best8.);
	endcomp;
	compute after age / style=Header{just=l};
	   line 'Student Count for This Average:' grpcnt ;
	   line 'Names for this Group:';
	   line grpnames $varying300.;
	endcomp;
  run;
ods html close;
title; options byline;

Hope this helps point you in a direction.

 

Cynthia

denperp
Obsidian | Level 7

Thank so much!  I wasn't able to get the macro called up like you did in your example (maybe a character. vs. numeric or formatting issue? - I'm still new with macros), but I was able to create a new data table using the first function and the escapechar and merged that back into the file and reported that out in a new column and that will do OK.

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 4 replies
  • 818 views
  • 0 likes
  • 2 in conversation