BookmarkSubscribeRSS Feed
B2SAS
Calcite | Level 5

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.

6 REPLIES 6
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Calcite | Level 5

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

data_null__
Jade | Level 19

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
Calcite | Level 5

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
Super User

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
SAS Super FREQ

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 993 views
  • 0 likes
  • 5 in conversation