seeking help on PROC report.

[B2SAS]

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.

[RW9]

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.

[B2SAS]

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

[data_null__]

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.

[B2SAS]

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.

[Ksharp]

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'  

.......... 

   ;                 

[Cynthia_sas]

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