Help using Base SAS procedures

seeking help on PROC report.

Reply
Occasional Contributor
Posts: 7

seeking help on PROC report.

Hi All, I am novice to SAS and trying to generate a report using PROC report. My requirement is to generate the summary report based on number of users added in last 12 months and display their totals:

Sample report:

OPEN                       OPEN

YEAR      OPEN             TOTAL

          MONTH

   -----------------------------

   2014   08                  46

          09                  42

          10                  33

          11                  61

          12                  50

   2015   01                  32

          02                  34

          03                  65

          04                  44

          05                  57

          06                  51

          07                  41

          08                  13

-----                         -----

TOTAL                         569

This is working fine as long as the onen month is populated as numeric values such as 01,02,03 etc. When I try to format the numeric month value with the corresponding alphabetic name, I am getting a different order. I tried all the options that I have known and of no use.

2014   AUGUST        46

        DECEMBER      50

        NOVEMBER      61

        OCTOBER       33

        SEPTEMBER     42

2015   APRIL         44

        AUGUST        14

        FEBRUARY      34

        JANUARY       32

        JULY          40

        JUNE          51

        MARCH         65

        MAY           57

-----             -----

TOTAL               569

my proc report sample code is given below:

PROC SORT DATA=USER_ENTRY;                                          

       BY OPEN_YEAR OPEN_MONTH_YYMM OPEN_MONTH;                       

                                                                      

PROC REPORT DATA = USER_ENTRY SPACING= 2 HEADSKIP HEADLINE NOCENTER; 

    

     TITLE  "                                                        ";

     TITLE3 "NUMBER OF USERS OPENED IN LAST 12 MONTHS";           

     WHERE OPEN_DATE > "&DATE_12MTH";                                 

     COLUMNS OPEN_YEAR OPEN_MONTH N;                                  

     DEFINE OPEN_YEAR   /GROUP 'YEAR  ' WIDTH=05;                     

     DEFINE OPEN_MONTH  /GROUP  'MONTH ' WIDTH=09                

                                FORMAT = $OPEN_MONTH_FMT.;             

                                                        

     DEFINE N /  'TOTAL ' WIDTH=05;                                   

     RBREAK AFTER /  SUMMARIZE OL;                                    

         COMPUTE AFTER;                                               

            OPEN_YEAR='TOTAL:';               

PROC FORMAT;   

VALUE $OPEN_MONTH_FMT     

  '01'      = 'JANUARY'   

  '02'      = 'FEBRUARY'  

  '03'      = 'MARCH'     

  '04'      = 'APRIL'     

  '05'      = 'MAY'       

  '06'      = 'JUNE'      

  '07'      = 'JULY'      

  '08'      = 'AUGUST'    

  '09'      = 'SEPTEMBER' 

  '10'      = 'OCTOBER'   

  '11'      = 'NOVEMBER'  

  '12'      = 'DECEMBER'  

   ;                      

Please help me solving this issue.

Super User
Super User
Posts: 7,955

Re: seeking help on PROC report.

Hi,

I would add into your define (assuming the dataset is already sorted!):

     DEFINE OPEN_MONTH  / GROUP order=data 'MONTH ' WIDTH=09               

                                FORMAT = $OPEN_MONTH_FMT.;

This will show the data items as they appear in the dataset.

Note, please don't code all in uppercase, it just makes it harder to read.

Occasional Contributor
Posts: 7

Re: seeking help on PROC report.

Thank you very much RW9 for your solution. It worked fine.

Respected Advisor
Posts: 3,799

Re: seeking help on PROC report.

Your CHARACTER variable OPEN_MONTH has values with leading 0 and sorts properly on the INTERNAL value.  The default is ORDER=FORMATTED.  ORDER=DATA is not really what you want here.

Just add ORDER=INTERNAL to the DEFINE statement to achieve the desired result.  There is really no need to sort the data before using PROC REPORT.

Occasional Contributor
Posts: 7

Re: seeking help on PROC report.

Posted in reply to data_null__

Thank You data_null_; When I used ORDER=DATA, I was getting the result, with one exception. Later, when I used ORDER=INTERNAL, I got my desired results. As you indicated, I removed PROC SORT steps.

Super User
Posts: 10,028

Re: seeking help on PROC report.

A workaround way is adding white blanks before it .

VALUE $OPEN_MONTH_FMT     

  'JANUARY'   = '       JANUARY'     *<- First one ,add the most blanks before it. 

  'FEBRUARY'  = '    FEBRUARY'  

.......... 

   ;                 

SAS Super FREQ
Posts: 8,866

Re: seeking help on PROC report.

Hi,

not all destinations will respect leading blanks, though, so that is a trick that is generally a LISTING only trick and not for ODS.

cynthia

Ask a Question
Discussion stats
  • 6 replies
  • 386 views
  • 0 likes
  • 5 in conversation