topic Re: seeking help on PROC report. in SAS Procedures

seeking help on PROC report.

B2SAS — Tue, 18 Aug 2015 13:51:34 GMT

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.

Re: seeking help on PROC report.

RW9 — Tue, 18 Aug 2015 14:07:24 GMT

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.

Re: seeking help on PROC report.

B2SAS — Tue, 18 Aug 2015 14:36:13 GMT

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

Re: seeking help on PROC report.

data_null__ — Tue, 18 Aug 2015 14:36:29 GMT

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.

Re: seeking help on PROC report.

B2SAS — Tue, 18 Aug 2015 14:41:48 GMT

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.

Re: seeking help on PROC report.

Ksharp — Fri, 21 Aug 2015 09:24:12 GMT

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'

..........

;

Re: seeking help on PROC report.

Cynthia_sas — Sat, 22 Aug 2015 00:44:17 GMT

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