The SAS Output Delivery System and reporting techniques

how do I report a list used in an analyses

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

how do I report a list used in an analyses

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
  

 


Accepted Solutions
Solution
Thursday
SAS Super FREQ
Posts: 9,423

Re: how do I report a list used in an analyses

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


All Replies
SAS Super FREQ
Posts: 9,423

Re: how do I report a list used in an analyses

[ Edited ]

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

New Contributor
Posts: 3

Re: how do I report a list used in an analyses

Posted in reply to Cynthia_sas

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!

Attachment
Solution
Thursday
SAS Super FREQ
Posts: 9,423

Re: how do I report a list used in an analyses

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

New Contributor
Posts: 3

Re: how do I report a list used in an analyses

Posted in reply to Cynthia_sas

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.

☑ This topic is solved.

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

Discussion stats
  • 4 replies
  • 118 views
  • 0 likes
  • 2 in conversation