how do I report a list used in an analyses

[denperp]

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:

		2013	2014	2015
		Target	Team			Target	Team			Target	Team
Region	Department	Sales	Sales	SalesRatio	Number in Peer Group	Sales	Sales	SalesRatio	Number in Peer Group	Sales	Sales	SalesRatio	Number in Peer Group
Latin America	Household	8000	7000	114%	8	7500	7500	100%	10	6000	10000	60%	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

[Cynthia_sas]

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:

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]

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 · Accepted solution]

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]

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.