PROC REPORT

8月 042016
 

Color Names, Formats, Macros, ODS, Excel®, and PROC REPORTNot too long ago I had a report generation request from an Alaska state agency. The request had some very specific requirements that detailed the use of user defined colors (by name), data driven control of the report, and Excel delivery using ODS and PROC REPORT. Along the way I had to: determine what colors are known to SAS by name; develop a user tool for color selection that would feed uniformly into a data driven table; construct formats based on the data that would match the user selected colors to specific types of report cells; and deliver the report using Excel. The process was interesting enough that it has resulted in two papers, which I will present at WUSS, September 7-9 in San Francisco.

In these two papers the process for discovering how to name, select, and display the colors is described. Formats are built from the color names and the reported data (DATA steps with two SET statements are used to perform a data merge). Traffic lighting is used is used at the cell level in PROC REPORT, and the whole process is driven by macros. Talk about a fun project! For those of you unable to join me in San Francisco for the full talk, here are two quick tips on how to do this.

Extracting color names from the SAS Registry

The names of available colors are stored in the SAS Registry. They can be extracted to a file through the use of PROC REGISTRY. The following REGISTRY step writes the list of color names and their HEX definitions to the text file COLORNAMES.TXT.

proc registry export= "colornames.txt" 
  usesashelp 
  startat='COLORNAMES';
   run;

Displaying a list of colors

If a list of color names is stored in a SAS data set, those colors can be displayed using PROC REPORT. In the following REPORT step, an Excel spreadsheet is created that shows the list of available colors that are stored in WORK.COLORS both with and without the color in the cell background.

proc report data=work.colors;
  column  colorname colorname=clr; 
  define colorname/ 'Color Name';
  define clr  / 'Color';
  compute clr /char length=35; 
     call   define(_col_,'style','style={background='||colorname||'}');
  endcomp;
  run;

I hope you enjoyed these tips. Please join me for the complete talk at the Western Users of SAS Software Educational Forum and Conference, September 7-9 at the Grand Hyatt on Union Square in San Francisco.  Discounted registration is still available through August 8th! Register now. It’s a great three days of educational opportunities, 100+ presentations, classes, workshops, networking and more.

Visit wuss.org for more information.

Editor's note: If you can't attend WUSS 2016 but would like to view Art's paper in it's entirety, the conference has plans to publish conference proceedings after the event.  

tags: ods, PROC REPORT, SAS ODS, US Regional Conferences, wuss

Tips for working with Color Names, Formats, Macros, ODS, Excel®, and PROC REPORT was published on SAS Users.

7月 112014
 

Did you inherit code that was written eons ago?  Do you find old programs to copy the PROC REPORT code and then simply change the variable names for your new program?  Have you wondered what all of those options do?  Do you ever send output to the Listing destination (the Output Window) anymore?

If you said yes to any of the questions above -- clean it up!  PROC REPORT has been around forever, but it has changed and evolved over the years to facilitate the creation of nicer looking output, specifically in non-Listing ODS destinations.

There are a number of options that are only valid in the Listing destination. Do you really need them? If you aren’t using that destination, then remove any outdated options from your SAS programs. Here’s a list by statement in the REPORT procedure:

PROC REPORT statement: outdated options

BOX NOWD/NOFS *
COLWIDTH= PANELS=
FORMCHAR= PS=
HEADLINE PSPACE=
HEADSKIP SPACING=
LS= WRAP

Nonwindowing mode became default in 9.4.

DEFINE statement: outdated options

CENTER * RIGHT *
FLOW SPACING=
LEFT * WIDTH= **

* In the Listing destination the LEFT, RIGHT, and CENTER options change the justification of both the headers and the data.  In other ODS destinations these options change only the justification for the data.  Instead of these options, it is highly recommended that you use the JUST= attribute within STYLE(COLUMN)= and STYLE(HEADER)= to change the justification.

** The standalone WIDTH= option is for Listing.  WIDTH= within a style specification, like STYLE(COLUMN)=[WIDTH=1IN], is for other ODS destinations.

BREAK statement: outdated options

DOL SKIP
DUL UL
OL

RBREAK statement: outdated options

DOL SKIP
DUL UL
OL

You can leave these options in, but why?  Make your PROC REPORT code more readable for yourself and anyone else who picks it up.  

 

tags: Problem Solvers, PROC REPORT, SAS Programmers
3月 142014
 

Like every SAS procedure, PROC REPORT generates error messages that are specific to that procedure.  Some of these errors are easier to understand and work around than others.  In this blog post, I show six of the trickiest errors, explain what might be causing the error, and give advice for how to circumvent it.

#1.  ACROSS variable not defined

proc report data=sashelp.class;
column sex, height;
run;

ERROR: There is more than one ANALYSIS usage associated with the column defined by the following elements.

A comma in the COLUMN statement indicates that you want a variable to be used as an ACROSS variable, i.e. one variable stacked on top of another.  This error will occur if your program doesn’t include a DEFINE statement with a usage of ACROSS.

Circumvention: Add a DEFINE statement for the ACROSS variable.

proc report data=sashelp.class;
column sex, height;
define sex /across;
run;

#2.  DISPLAY or GROUP variable needs its own column

proc report data=sashelp.class;
 column sex, height;
 define sex /across;
 define height /group;
run;

ERROR: A DISPLAY or GROUP variable above or below an ACROSS variable requires that there be an ORDER, GROUP, or DISPLAY variable in the report that is not above or below an ACROSS variable.

Often a report requires an ACROSS variable with a character and an analysis variable underneath it. PROC REPORT can create this report, but it has to have a DISPLAY or GROUP variable that is its own column.

Circumvention: Add a grouping variable and place it before the ACROSS variable on the COLUMN statement. The new variable can be defined as NOPRINT so that it will not be seen in the report.

data class;
 set sashelp.class;
 dummy=1;
proc report data=class;
 column dummy sex, height ;
 define dummy /group noprint;
 define sex /across;
 define height /group;
run;

#3.  Variable below ACROSS variable defined as DISPLAY

proc report data=sashelp.class;
 column age sex, height;
 define age /group;
 define sex /across;
 define height /display;
run;

ERROR: There is no statistic associated with XXXX.

This error message is generated when the only variable below an ACROSS variable is defined as DISPLAY.

Circumvention:  Change the DISPLAY variable to be GROUP or add the N statistic after the ACROSS grouping on the COLUMN statement.

proc report data=sashelp.class;
 column age sex, height;
 define age /group;
 define sex /across;
 define height /group;
run;

#4.  Multiple summary rows on the same variable or location

proc report data=sashelp.class;
 column sex sex=sex2 weight;
 define sex /group;
 define sex2 /group;
 break after sex /summarize;
 break after sex2 /summarize;
run;

ERROR: The BREAK variable XXXX is not one of the GROUP or ORDER variables.

PROC REPORT does not have the ability to have multiple summary rows on the same variable or location. You’ll receive this error message if you have used one variable twice on the COLUMN statement (once using an alias), defined it as GROUP or ORDER, and there is a BREAK statement for both the variable and its alias.

Circumvention: Create a new variable in a DATA step that is a copy of the variable and use the new variable on the COLUMN statement instead of an alias.

data class;
 set sashelp.class;
 sex2=sex;
proc report data=class;
 column sex sex2 weight;
 define sex /group;
 define sex2 /group;
 break after sex /summarize;
 break after sex2 /summarize;
run;

#5.  Two different usages for the same variable

proc report data=sashelp.class;
 column age sex name age=age2;
 define age /group;
 define sex /group;
 define age2 /display;
run;

ERROR: XXXX conflicts with earlier use of XXXX.

In some circumstances PROC REPORT will not let you have two different usages for the same variable. In the example above, the variable age cannot be both GROUP and DISPLAY. An alias is most useful when you have an analysis variable that you want multiple statistics for, such as mean, min, or max. However, you can use an alias on any type of variable, but you do have to be careful about the usage issue.

Circumvention: Create a new variable in a DATA step that is a copy of the variable. This way the new variable contains the same information but can be used in any way you choose in the PROC REPORT step.

data class;
 set sashelp.class;
 age2=age;
proc report data=class;
 column age sex name age2;
 define age /group;
 define sex /group;
 define age2 /display;
run;

#6.  No format specified for LINE statement variable

proc report data=sashelp.class;
 column sex name age;
 define sex /group noprint;
 compute before sex;
   line 'this is for gender group ' sex;
 endcomp;
run;

ERROR: XXXX must use a character format.

This error normally occurs when a character variable is used on a LINE statement, and no format is specified after it. On a LINE statement you must specify a format for each item (variable).

Circumvention: Add an appropriate format after the variable on the LINE statement.

proc report data=sashelp.class;
 column sex name age;
 define sex /group noprint;
 compute before sex;
   line 'this is for gender group ' sex $1.;
 endcomp;
run;

See the REPORT procedure documentation for details on each of these statements.
tags: Problem Solvers, PROC REPORT, SAS Programmers
1月 232014
 
Want to impress others with professional reports of any level and any amount of detail through creative techniques?  Want to tackle each report put before you and break each challenge down into manageable pieces? Lisa Fine’s new book PROC REPORT by Example: Techniques for Building Professional Reports Using SAS is [...]
4月 252012
 

Disasters happen every day. Often times they occur at inconvenient hours and in remote locations. So it’s important to have a plan - before the emergency - to get qualified personnel to those locations in the most efficient way. Pilots are an example of qualified personnel who could act as “first responders” in these situations.

At SAS Global Forum 2012, I saw a presentation by Andrew Hummel, General Manager, Crew Resources at Delta Air Lines about his use of SAS to create a US “First Responders Directory” by aligning pilot residences with airport locations. Now that’s preparation for the unknown.

Delta Air Lines operates formal corporate emergency response teams, but those are often set up in centralized locations and may not be as effective when responding to disasters in rural areas or during non-business hours. That’s where the pilots come in. Throughout the US, Delta Air Lines has more than 10,000 pilots who could act as first responders to the more than 250 airports that are sites designated to respond to emergencies. Identifying which pilots live the closest to airports through a directory is critical information during a crisis when those pilots could be called to drive to the airport immediately, at any time of day.

How did Delta Air Lines create the directory? SAS can calculate the distance (in miles) between two points through ZIP codes and longitudinal and latitudinal data. The team first gathered longitudinal and latitudinal coordinates for each airport and ZIP codes of pilot’s homes – all data found within a Teradata data warehouse.

And we're talking about a ton of data here, given the number of pilots across the country. But SAS is able to compute the resulting 3 million calculations in a matter of seconds. The final directory lists how far each pilot lives from each airport, see example report created by PROC REPORT below (click to enlarge).

 

Delta Air Lines can then quickly match first responders to any emergency location in the US.

Check out the full paper to see exactly how the report was developed. Can SAS be applied to create critical information for a crisis in your industry? Feel free to share those examples in the comments.

tags: papers & presentations, PROC REPORT, PROC SQL, SAS Global Forum, teradata
4月 132012
 
I’m a SAS user in the Pharmaceutical industry. I switched to the Pharmaceutical industry (from Marketing Research) four years ago and had a lot to learn! I started my new endeavor by purchasing some excellent SAS books, joining my local SAS user group, attending conferences (Michigan SAS User Group, PharmaSUG, [...]
4月 132012
 
I’m a SAS user in the Pharmaceutical industry. I switched to the Pharmaceutical industry (from Marketing Research) four years ago and had a lot to learn! I started my new endeavor by purchasing some excellent SAS books, joining my local SAS user group, attending conferences (Michigan SAS User Group, PharmaSUG, [...]
1月 102012
 
Dear Miss SAS Answers, In PROC REPORT can I use one calculated (computed) variable in the calculation of another computed variable? In the example below, I’m trying to use the value of the Bonus column to calculate the Total column:   compute Bonus;     Bonus =sal.sum*0.05;   endcomp;   compute Total;     total=sum(sal.sum, Bonus.sum);   endcomp; [...]
12月 242011
 
Ron Cody and his supremely helpful book Learning SAS by Example close out our weekly SAS Author's Tip of 2011. Ron Cody is a household name in the SAS user community. And he deserves the moniker on his license plate of "SASMAN". Just mentioning Ron's name at user conferences incites awe. Sometimes [...]
10月 032011
 
Most SUG presentations are written in PowerPoint – they may even be written in Word first, and then fancied up a bit in PowerPoint – but they are rarely written in SAS. But Louise Hadden, from Abt Associates Inc, had a need to produce a lot of PDF presentations. She [...]