As the first step in the decommissioning of sasCommunity.org the site has been converted to read-only mode.


Here are some tips for How to share your SAS knowledge with your professional network.


How to Display values for every row of an Order or Group Report-Item in Proc Report without using an Alias

From sasCommunity
Jump to: navigation, search

If you want to display all the values of an Order or Group report item, you can create a duplicate copy of the variable of interest in a Data step and then individually define or use the two variables in your Proc Report.

In Proc Report, you cannot have multiple different usage of a report item even when using an alias. Instead of an alias, create a copy of the variable of interest so you can use the variables differently to display the output that you want.

In Example 3: Using Aliases to Obtain Multiple Statistics for the Same Variable of the Base Procedures Guide examples, the output looks something like:

                Sales for the Southeast Sector                1
                         for 30MAR10
                 Manager  Department    Sales
                 ----------------------------
                                             
                 Jones    Paper        $40.00
                          Canned      $220.00
                          Meat/Dairy  $300.00
                          Produce      $70.00
                 Smith    Paper        $50.00
                          Canned      $120.00
                          Meat/Dairy  $100.00
                          Produce      $80.00
                                                               
     -----------------------------------------------------     
     | Departmental sales ranged from $40.00 to $300.00. |     
     -----------------------------------------------------     

Say you want Manager to appear in every row. If you use DISPLAY for Manager and/or Department instead, you may not get exactly the same order as the output above unless you have pre-sorted your data before getting it into Proc Report.

The code below which uses an ALIAS for Manager would create a conflict (ORDER vs. DISPLAY):

proc report data=grocery nowd headline headskip;
    column manager=mgr manager department sales
          sales=salesmin
          sales=salesmax;
    define mgr / display
                    order=formatted
                    format=$mgrfmt.
                    'Manager';
    define manager / order
                    order=formatted
                    format=$mgrfmt.
                    'Manager' noprint;
    define department / order
                    order=internal
                    format=$deptfmt.
                    'Department';
    define sales / analysis sum format=dollar7.2 'Sales';
    define salesmin / analysis min noprint;
    define salesmax / analysis max noprint;
     compute after;
      line ' ';
      line @7 53*'-';
      line @7 '| Departmental sales ranged from'
           salesmin dollar7.2  +1 'to' +1 salesmax dollar7.2
           '. |';
      line @7 53*'-';
     endcomp;
     where sector='se';
     title 'Sales for the Southeast Sector';
     title2 "for &sysdate";
run;

SOLUTION: Create a copy of Manager so you can use it differently in the report and use the NOPRINT option for the second report item for Manager.

data new;
  set grocery;
  dummy=manager;
run;
 
proc report data=new nowd headline headskip; 
     column dummy manager department sales
          sales=salesmin
          sales=salesmax; 
     define dummy / display
                    order=formatted
                    format=$mgrfmt.
                    'Manager';
     define manager / order
                    order=formatted
                    format=$mgrfmt.
                    'Manager' noprint;
     define department / order
                    order=internal
                    format=$deptfmt.
                    'Department'; 
     define sales / analysis sum format=dollar7.2 'Sales'; 
     define salesmin / analysis min noprint;
     define salesmax / analysis max noprint; 
     compute after;
      line ' ';
      line @7 53*'-'; 
      line @7 '| Departmental sales ranged from'
           salesmin dollar7.2  +1 'to' +1 salesmax dollar7.2
           '. |';
      line @7 53*'-';
     endcomp; 
     where sector='se'; 
     title 'Sales for the Southeast Sector';
     title2 "for &sysdate";
run;

The output should now have the values for Manager for every row:

                Sales for the Southeast Sector                2
                         for 30MAR10
                 Manager  Department    Sales
                 ----------------------------
                 Jones    Paper        $40.00
                 Jones    Canned      $220.00
                 Jones    Meat/Dairy  $300.00
                 Jones    Produce      $70.00
                 Smith    Paper        $50.00
                 Smith    Canned      $120.00
                 Smith    Meat/Dairy  $100.00
                 Smith    Produce      $80.00
     -----------------------------------------------------
     | Departmental sales ranged from $40.00 to $300.00. |
     -----------------------------------------------------


Submitted by Philamer Atienza. Contact me at my Discussion Page.

....see also